Fast SQL, slow storedproc?

I have a storedproc that takes "forever" to run. However, the SQL inside is
fairly simple, consisting of a series of SELECT...INTOs that build up a table
only 2500 rows long. There's only one time consuming query, and when I run
that by hand it only takes eight seconds, the other seven inside finish in
less than a second. That jives with what I think should happen, it should
take maybe 15 seconds to run, but instead times out after 2 minutes.
Can anyone offer some suggestions here?
MauryDo you use sp_executesql somewhere? if yes, then you've run into the same
problem I posted a few minutes ago...
"Maury Markowitz" wrote:
> I have a storedproc that takes "forever" to run. However, the SQL inside is
> fairly simple, consisting of a series of SELECT...INTOs that build up a table
> only 2500 rows long. There's only one time consuming query, and when I run
> that by hand it only takes eight seconds, the other seven inside finish in
> less than a second. That jives with what I think should happen, it should
> take maybe 15 seconds to run, but instead times out after 2 minutes.
> Can anyone offer some suggestions here?
> Maury|||"Jochen Wezel" wrote:
> Do you use sp_executesql somewhere? if yes, then you've run into the same
> problem I posted a few minutes ago...
I'm not sure what that is, but the SP has nothing but selects in it (with
two parameters) and I call it thus:
exec pGenerateHPLPriceList '8/24/07', 13
Maury|||Ahh...
Taking a hint from your other thread, I googled up "recompile". Try this...
exec sp_recompile yourprocnamehere
Mine went from 3:09 to 0.06. Might want to try it :-)
Maury|||Okay, then it might be another problem. Sorry.
"Maury Markowitz" wrote:
> "Jochen Wezel" wrote:
> > Do you use sp_executesql somewhere? if yes, then you've run into the same
> > problem I posted a few minutes ago...
> I'm not sure what that is, but the SP has nothing but selects in it (with
> two parameters) and I call it thus:
> exec pGenerateHPLPriceList '8/24/07', 13
> Maury

Fast Record Count

I think I am asking this in the right place.

I'm trying to get a record count from a table with 30million items and it takes forever.

Here is my code:

SELECT COUNT(f_id) AS 'ROWCOUNT' FROM tablename

Is there a faster way.

BTW f_id is primary key indexed.

Thanks

Alright Chap,

Try this,

use database_name

go

select object_name(id), rowcnt from sysindexes

where object_name(id) = 'tablename' and indid in (0,1)

regards

Jag

|||

Jag's method is quick, reading directly from the indexes table.

However, use caution, for just as indexes get out of sync with the data, the rowcnt in sysindexes can be wrong. So if a 'quick an dirty' report is useful (with the chance of some inaccuracy), it is a quick method.

If you desire an accurate count, there is really no other way than counting from the data table. Again counting an indexed column (or the primary key) 'could' be inexact.

|||

Ok thanks

Yes this was a million X faster.

And Arnie thanks for the input too. For this quest a close ammount is good for me. + or - a few mil is fine..lol

Thanks guys

|||To clarify Arnie's statement:

if you are using SQL2000, it is very easy for the numbers to get out-of-sync, and I would not rely on it.

If you are using SQL2005, the number should be correct, as we did fix some issues that made the number correct.

To get the number correct after you upgraded from SQL2000, you should update the statistics on the table.

Thanks,|||

Thanks Marcel,

I should have prefaced my comments with a clear indication I was referring to SQL 2000.