Query is timing out :( help plz

Question

Query is timing out :( help plz

Viewing 12 posts - 16 through 26 (of 26 total)

You must be logged in to reply to this topic. Login to reply

Bhuvnesh SSC Guru Points: 59351 More actions · Answer 1

Does it mean that if space available and for temporary storing purpose, we can store milions of record in table variable? (this is just hypothentical scenario )

and second ques, whenever we have any temp table ,sql engine creates automatically statistics on it?

-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 2

Bhuvnesh (2/3/2010)
Does it mean that if space available and for temporary storing purpose, we can store milions of record in table variable? (this is just hypothentical scenario )

There is no limit on the number of rows in a table variable. You can put as many rows into a table variable as you can a temp table or normal (permanent) table.

and second ques, whenever we have any temp table ,sql engine creates automatically statistics on it?

SQL will create stats on temp tables when it needs them. That's temp tables, not table variables

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Bhuvnesh SSC Guru Points: 59351 More actions · Answer 3

Thanks a lot

-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)

Lebanese SSC-Addicted Points: 461 More actions · Answer 4

Below is the Statistics

Client Execution Time09:05:43

Query Profile Statistics

Number of INSERT, DELETE and UPDATE statements00.0000

Rows affected by INSERT, DELETE, or UPDATE statements00.0000

Number of SELECT statements 11.0000

Rows returned by SELECT statements11.0000

Number of transactions 00.0000

Network Statistics

Number of server roundtrips33.0000

TDS packets sent from client33.0000

TDS packets received from server1515.0000

Bytes sent from client18321832.0000

Bytes received from server5018650186.0000

Time Statistics

Client processing time00.0000

Total execution time140140.0000

Wait time on server replies140140.0000

the Execution Plan is attached.

thank you so much

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 5

Those aren't IO Statistics.

For the IO statistics, run SET STATISTICS IO ON, then run the query.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Lebanese SSC-Addicted Points: 461 More actions · Answer 6

Lebanese

SSC-Addicted

Points: 461

February 3, 2010 at 8:12 am

#1112930

Attached.

Thanks

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 7

We're not asking for the client statistics. We're asking for the IO statistics which are completely different.

Run the command

SET STATISTICS IO ON

Then run the query. The IO stats will be outputted to the messge pane.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Lebanese SSC-Addicted Points: 461 More actions · Answer 8

i'm sorry GilaMonster

i hope this time is the correct one

(171 row(s) affected)

Table 'Menu_Descriptions'. Scan count 1, logical reads 2, physical reads 1, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Table 'REGION_CODES'. Scan count 1, logical reads 2, physical reads 1, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Table 'Menu_Reporting'. Scan count 5, logical reads 219666, physical reads 175, read-ahead reads 218931, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Bhuvnesh SSC Guru Points: 59351 More actions · Answer 9

can u post different indexes associated with tables?

. i guess someone had asked for the same earlier.

-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)

TheSQLGuru SSC Guru Points: 134017 More actions · Answer 10

1) Dynamic sql

2) better indexing

Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service

Lebanese SSC-Addicted Points: 461 More actions · Answer 11

Lebanese

SSC-Addicted

Points: 461

February 5, 2010 at 1:43 pm

#1114757

any inputs on why it is failing?

TheSQLGuru SSC Guru Points: 134017 More actions · Answer 12

You posted over 150 lines of stuff in the original post. Forums are useful for helping out with targeted, fairly straight forward/simple problems and are 'worked' by people who are freely giving of their time. 🙂 Your post could well take an hour or more to decipher and understand effectively and then possibly days to "get right". Seems like an opportunity for some consulting to help you out. My 0.02.

Having said that I believe that one or both of my suggestions will be part of your optimal solution. But getting there is likely not a trivial endeavour.

Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service