can you query against results set from a stored procedure?

[sobeit]

For example:
select OrderID, Order Descr
from Order where OrderID IN(execute uspOrderByDemographic)

 

[fluteplr]

No, but if you are using SQL 2000 you can create a user function that does the same thing as the stored procedure.

 

[Deeba]

Or you could save the results from the stored procedure to a temporary table.

 

[sobeit]

Temp table is what i've decided on, but there
is other problem now.

Thanks.

 

[essa2000]

Dear ;

What kind of other problem you have now? Please , write details of that problem so that we can see into it .

regards,
essa2000

 

[sobeit]

I would love to paste my query in this e-mail, but
there are about 23 tables in the JOIN clauses AND
that may exclude a View, which is contributing to the poor performance of this query.

Extracting data from this View directly takes less than 5 seconds, and running a query without joining to the view (left join) takes about 25 seconds. But when including the view, the query takes more than 48 seconds to complete.

Like to take it down to at least 30 seconds. Any idea why joining to a view in a query takes much longer to execute?

Thanks for listening by the way.

chow.

 

[ColinM]

Would it be possible to denormalize your table for a better performance?
Also do you having indexes on the fields you are joining will obviously help.
Perhaps you could index the view?

 

[sobeit]

Denormalize?...I just try not to do that, especially with some of the developers we have. They'll decide on denormalize a table without examining the bigger picture.

index view would be great if we were using SQL 2000 Enterprise Edition.

so what I am trying to do now is to use one more temp table to hold the final result set, and loop through a cursor that have the desired key sets and with a join..insert the
appropriate data set into my final temp table.

It's what I am use to do with Visual FoxPro, and Cursor and Temp tables are suppose to be avoide in SQL Server..but if I can get it to return less than 20 seconds, then i am going with it.

Thanks you all for your suggestions and comments.