Multiple stored procedures,single report

Hi ,

I have a Report which has 8 stored procedures to get 8 resultant data sets . the stored procedures are almost similar such that they have only difference is the where clause and column getting returned.

So ,every stored access same set of tables and temporary tables getting created to store some set of active data derived from big table.

what happening is ,when i run the stored procedures individually in query analyser ,it is taking the time which is accepatable individually,but when i keep them in the same report. it is taking the time which is equal to sum of all the times taken by the stored procedures ran individually in query analyser which is some what not acceptable

can anybody through an idea,what can be done here. i already thought of locks and kept set transaction isolation level read uncomitted for all the stored procedures.but the time taking is same.

please help me here,i am stuck

Thank you

This is somewhat interesting -- the usual complaint is "it ran okay in the query analyzer, why does it run so much slower in the report". Here you're saying "it pretty much runs exactly the same way in the report as the sum of the times in the query analyzer, and I don't like that" <g>.

So the first thing to do is to profile in the query analyzer (use the execution plans) and see what you need to optimize about that query. It sounds like whatever is in your multiple WHERE conditions needs indexes or something.

Another thing that comes to mind is checking the state of the database, especially if this db was up-converted from SQL 2000, using DBCC. I would especially look into indexes and if it is an up-convert use DBCC UPDATEUSAGE(0).

Be that as it may, this might be a situation in which we have a different way to handle it for reports. Ordinarily this is not recommended, but in your case it might work great. Here is what I have in mind:

* -- Instead of 8 procs, have one proc that returns all 8 columns and a where clause that (probably with ORs) returns the full set of data you need for your 8 tables. Now you're only going against the database once.

* -- now have your layout tables re-interpret your parameters or wherever you're getting your WHERE clauses, to filter each displayed set of data appropriately for the column they represent.

Note: proper indexes on the table, if they are not already present, will still help for that initial data pull and are still indicated. If you already have the proper indexes, try rebuilding them...

>L<

Multiple stored procedures,same set of tables

Hi ,

I have a Report which has 8 stored procedures to get 8 resultant data sets . the stored procedures are almost similar such that they have only difference is the where clause and column getting returned.

So ,every stored access same set of tables and temporary tables getting created to store some set of active data derived from big table.

what happening is ,when i run the stored procedures individually in query analyser ,it is taking the time which is accepatable individually,but when i keep them in the same report. it is taking the time which is equal to sum of all the times taken by the stored procedures ran individually in query analyser which is some what not acceptable

can anybody through an idea,what can be done here. i already thought of locks and kept set transaction isolation level read uncomitted for all the stored procedures.but the time taking is same.

please help me here,i am stuck

Thank you

first identified the common parameters from All Queries. and create a new Query and move all data in a Temp table.

and apply where clause on that temp table. this will reduce your query time.

Example.

select *

into #temp

from table1 inner join table2 on table1.t1 = table2.t1

where x= 'abc'

select * from #temp where a='aaa'

select * from #temp where a='aad'

select * from #temp where a='aaf'

select * from #temp where a='aag'

select * from #temp where a='aagg' and b ='aaa'

drop table #temp

from that way you hit database only one time and rest of the queries will be perform on small temparary data table.

|||

As long as concurrency isn't a problem, I would consider having one stored procedure build up a permanent table in tempdb, and then have the subsequent calls use that object. It will require timing to make sure that your child stored procedures don't start before the first one gets started, but it will give you the set of data (with indexes, if you need them) that can be accessed across any number of processes.

Of course, since the temp table has a given name, you can't have multiple processes running the report at the same time, You could use dynamic SQL if you had to and use a GUID for the reporting table's name in that case, though it would significantly ugly up your code.

Multiple row insert only inserting one row?

I can't seem to find any place that a similar issue has been
encountered, so here goes:
INSERT INTO destination(dest_id)
SELECT src_id
FROM source
WHERE NOT EXISTS (SELECT dest_id FROM destination WHERE dest_id = src_id)
The subquery executes as expected when run by itself, returning [n]
rows. But when I try to execute the insert statement, only one row gets
inserted. Or rather, it inserts one record, then runs the SELECT
statement almost as if it was entirely separate:
(1 row(s) affected)
([n - 1] row(s) affected)
I should note that originally I was trying to insert several fields from
the source table, but was experiencing the same behavior -- only one row
getting inserted -- but some of the fields weren't getting inserted
properly either. So I pared it down to trying to just get the ID's
inserted, after which I planned on running an UPDATE.
Any advice on what I might be missing would be appreciated.Also, I tried the following to no avail:
* wrapping the SELECT statement in parentheses
* adding TOP 100 PERCENT to the SELECT
* changing the NOT EXISTS to a NOT IN () statement
Matthew Harward wrote:
> I can't seem to find any place that a similar issue has been
> encountered, so here goes:
> INSERT INTO destination(dest_id)
> SELECT src_id
> FROM source
> WHERE NOT EXISTS (SELECT dest_id FROM destination WHERE dest_id = src_id)
> The subquery executes as expected when run by itself, returning [n]
> rows. But when I try to execute the insert statement, only one row gets
> inserted. Or rather, it inserts one record, then runs the SELECT
> statement almost as if it was entirely separate:
> (1 row(s) affected)
> ([n - 1] row(s) affected)
> I should note that originally I was trying to insert several fields from
> the source table, but was experiencing the same behavior -- only one row
> getting inserted -- but some of the fields weren't getting inserted
> properly either. So I pared it down to trying to just get the ID's
> inserted, after which I planned on running an UPDATE.
> Any advice on what I might be missing would be appreciated.
>|||Try using a table alias...
INSERT INTO destination(dest_id)
SELECT src.src_id
FROM source src
WHERE NOT EXISTS
(SELECT des.dest_id
FROM destination des
WHERE des.dest_id = src.src_id)
"Matthew Harward" <mharward@.alliedtradegroup.com> wrote in message
news:bNu1g.22056$DR1.8455@.fe27.usenetserver.com...
> I can't seem to find any place that a similar issue has been
> encountered, so here goes:
> INSERT INTO destination(dest_id)
> SELECT src_id
> FROM source
> WHERE NOT EXISTS (SELECT dest_id FROM destination WHERE dest_id = src_id)
> The subquery executes as expected when run by itself, returning [n]
> rows. But when I try to execute the insert statement, only one row gets
> inserted. Or rather, it inserts one record, then runs the SELECT
> statement almost as if it was entirely separate:
> (1 row(s) affected)
> ([n - 1] row(s) affected)
> I should note that originally I was trying to insert several fields from
> the source table, but was experiencing the same behavior -- only one row
> getting inserted -- but some of the fields weren't getting inserted
> properly either. So I pared it down to trying to just get the ID's
> inserted, after which I planned on running an UPDATE.
> Any advice on what I might be missing would be appreciated.
>|||Thanks for the quick reply. I tried both aliasing and using full
resolution - both with no luck:
INSERT INTO destination(dest_id)
SELECT src.src_id
FROM source src
WHERE NOT EXISTS
(SELECT des.dest_id
FROM destination des
WHERE des.dest_id = src.src_id)
and
INSERT INTO destination(dest_id)
SELECT source.src_id
FROM source
WHERE NOT EXISTS
(SELECT destination.dest_id
FROM destination
WHERE destination.dest_id = source.src_id)
Jim Underwood wrote:
> Try using a table alias...
> INSERT INTO destination(dest_id)
> SELECT src.src_id
> FROM source src
> WHERE NOT EXISTS
> (SELECT des.dest_id
> FROM destination des
> WHERE des.dest_id = src.src_id)
>
> "Matthew Harward" <mharward@.alliedtradegroup.com> wrote in message
> news:bNu1g.22056$DR1.8455@.fe27.usenetserver.com...
>|||Post DDL and soem sample data and I will try to duplicate the issue. It
looks to me like it should be working. You might try putting paranthesis
around your select.
"Matthew Harward" <mharward@.alliedtradegroup.com> wrote in message
news:Y3v1g.109156$gj5.69035@.fe16.usenetserver.com...
> Thanks for the quick reply. I tried both aliasing and using full
> resolution - both with no luck:
> INSERT INTO destination(dest_id)
> SELECT src.src_id
> FROM source src
> WHERE NOT EXISTS
> (SELECT des.dest_id
> FROM destination des
> WHERE des.dest_id = src.src_id)
> and
> INSERT INTO destination(dest_id)
> SELECT source.src_id
> FROM source
> WHERE NOT EXISTS
> (SELECT destination.dest_id
> FROM destination
> WHERE destination.dest_id = source.src_id)
> Jim Underwood wrote:
src_id)
from
row
>|||Actually, I figured it out ... there was a INSTEAD OF INSERT trigger
messing things up.
<sigh> Triggers always seem to be the last place I look, you'd think I'd
learn my lesson by now. ;)
Thanks again for the willingness to help.
Jim Underwood wrote:

> Post DDL and soem sample data and I will try to duplicate the issue. It
> looks to me like it should be working. You might try putting paranthesis
> around your select.
> "Matthew Harward" <mharward@.alliedtradegroup.com> wrote in message
> news:Y3v1g.109156$gj5.69035@.fe16.usenetserver.com...
> src_id)
> from
> row
>