How to debug the 200 lines of sql query
-
Dear Friends,
I have a very long query which is nearly 200 lines of sql code. We have nearly 250 tables in our database and we only use nearly 10 to 15 tables in this particular query. We have nearly 10 databases with same structure seperated according to the regional wise. For all the databases we get results very quickly within 10 mins. But whenever we execute for a particular database which I call as sample the tempdb is growing upto 2 GB and after that iam getting the tempdb space error. But for your information in this region we have subdivisions. So whenever i execute the query i will take the subdivision id from another table manually and i substitute in this query manually. For all other divisions iam getting the results but for a particular division iam getting the tempdb error. The no of records three main tables which will have huge records are given as follows.
Table A: 25,000 records
Table B: 17,000 records
Table C: 3500 records
Can you help me or atleast give me guidance on how to debug the query which is giving problems.
[font="Verdana"]Thanks
Chandra Mohan[/font] -
- open the query in query analyser and check for the estimated execution plan.
- how accurate are your statistics ?
(sp_updatestats / dbcc updateusage(0) with count_rows)
Then number of rows are certainly not high enough to be worried to start with.
Johan
Learn to play, play to learn !Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:- How to post Performance Problems
- How to post data/code to get the best help[/url]- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
-
Is this a single query?
-
another thought.
download a trialversion of redgate's SQL Refactor Maybe it can help out.
Johan
Learn to play, play to learn !Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:- How to post Performance Problems
- How to post data/code to get the best help[/url]- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
-
chandramohann, any kind of query from 3 tables containing under 50k rows all together should not take more than couple of seconds, unless you running it on a server with single CPU Pentium 100 and 8M of RAM.
Run SELECT * from all your tables and see how long it takes to read all data in your database.
10 mins - it's not quickly, it's in fact terribly slow.
I believe that report could be built faster in Excel.
To keep server occupied for 10 minutes your SQL statements must force a lot of writings, hell lot of writings. 10, 20 30 GB - I don't know how much your server can write in 10 minutes.
I don't think it should be a task of debugging SQL queries, it should be about reading books and designing database properly.
_____________
Code for TallyGenerator -
A query consisted of 200 rows? Wow...
Try to explain what the query do and your expected output from the query.
N 56°04'39.16"
E 12°55'05.25" -
Hi
I just had this sort of thing happen to me last week. The culprit was an OR operator that was not wery well thought out. A couple of parentheses fixed the problem. Before the fix, the query plan showed 1.582 x 10^10 rows as the result of an unintended cross join, which required a large amount of space in TempDB.
It was not a problem with the database design, just that one query. I think you should compare the execution plans of the query and see where the difference is between the two databases.
-
Two hundred lines of SQL query - you mean in one query?!
I wanted to know how the developer wrote this query. BTW what's wrong with this query that you need to debug it. I love to see this query.
It is challenging to debug something liked this.
-
Many OR's is a sign of bad design.
Probably not that bad, but anyway.
Those OR's indicate some business rules hardcoded in query instead of being placed into table(s).
If they would be in table(s) it would end up in join(s) instead of horrible set of OR's in WHERE clause.
_____________
Code for TallyGenerator
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply