Issues related to the speed of the execution of sql statement in mssql server 2005

Question

Issues related to the speed of the execution of sql statement in mssql server 2005

mailtoanishrnair

Newbie

Points: 1
More actions
April 29, 2008 at 1:59 am

#206811

I have created 2 tables in a database which are mostly similar, the table1 will execute with more speed (take only less than or equal to 1 sec) but the table2 will take 4 or 5 secs to execute the query,moreover the similar datas was presented in both the tables. the eg:- query that i have executed is select max(c_code) from table1 and select max(c_code) from table2, the first one take less than 1 sec and the second one take more than 4 or 5 secs, also there is a procedure i hve written to update both the tables, and i got the time out error, if the table1 alone is updated using the procedure it is OK but the table2 alone is updated using the procedure the time out error will be shown, pls reply the reason for this problem as early as possible, it will be a grateful if anybody reply for this trouble?

Viewing 5 posts - 1 through 5 (of 5 total)

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

Grant Fritchey SSC Guru Points: 399278 More actions · Answer 1

To give you a specific answer, sample structures and data and code are necessary. To give you a general answer, take a look at the execution plans for the queries against each table. You'll probably see that Table A is using an index in a seek operation (or something along those lines, again, I can't be specific with a lot more information) and Table B is doing a scan. You may have different data sets in the two tables. You may have different indexes. You may have different statistics on the two tables. You might have different index fragmentation on the two tables. You may have different structures on the two tables. All of these could lead to differences in performance.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

Jeff Moden SSC Guru Points: 1004642 More actions · Answer 2

That covers just about all of it... can't think of anything else to check unless the WHERE clauses have a formula in one and not the other.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

TheSQLGuru SSC Guru Points: 134017 More actions · Answer 3

Database collation settings could be different too.

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

RBarryYoung SSC Guru Points: 143329 More actions · Answer 4

And, the tables might be stored in different filegroups or files on different disks that have different performance characteristics or usage profiles.

[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung [font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]