October 4, 2011 at 1:25 am
sql server 2005 stand :
Select query talking 1.2 min for 7,80,000 records . I am getting performance issue.
What will i do for that. ?
Anyone could help for me
Thanks
Jerry
October 4, 2011 at 1:58 am
...What will i do for that. ? ...
Post the execution plan.
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
October 4, 2011 at 2:52 am
Read this article on how to post performance problems: http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
Post the required information and I'm sure lots of people will try to help.
-- Gianluca Sartori
October 4, 2011 at 4:24 am
You're returning a million records in a minute? Sounds like performance is OK. Why are you returning a million records?
"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
October 4, 2011 at 4:59 am
For reporting purpose, but all connection are suspended stauts, ather application getting time out issue web page,
Thanks
October 4, 2011 at 5:33 am
solomon.jernas (10/4/2011)
For reporting purpose, but all connection are suspended stauts, ather application getting time out issue web page,Thanks
Why don't you return the sum (or what ever aggregate) the final user needs to see? There's no way I'd waste my time going through 1M records manually.
October 4, 2011 at 6:49 am
Ninja's_RGR'us (10/4/2011)
solomon.jernas (10/4/2011)
For reporting purpose, but all connection are suspended stauts, ather application getting time out issue web page,Thanks
Why don't you return the sum (or what ever aggregate) the final user needs to see? There's no way I'd waste my time going through 1M records manually.
Agreed. No one is going to read 700,000 rows. Ever. They don't. You need to determine what's actually needed and then write your query to return just that.
"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
October 4, 2011 at 11:58 am
Chances are that 700K rows are used for some type of drill down report with a Summarization that is all anyone looks at. I know of reports that have over 2 million rows returned for this very reason. You don't need to look at everything all the time, but when the boss wants drill down you get these situations.
October 4, 2011 at 12:19 pm
But then you build the system to allow the drill-down to make another query. I've been through this too, at multiple organizations with multiple different data sets. There are any number of ways to deal with this that don't involve moving 700K off the database all at once. Especially when, moving 700K off the database, causes the app the timeout.
"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
October 4, 2011 at 1:38 pm
Grant Fritchey (10/4/2011)
But then you build the system to allow the drill-down to make another query. I've been through this too, at multiple organizations with multiple different data sets. There are any number of ways to deal with this that don't involve moving 700K off the database all at once. Especially when, moving 700K off the database, causes the app the timeout.
Also, take into account that 1M rows have to be stored somewhere and processed at the client side. This way, the boss must work with a monster computer rather than with those small stylish laptops that managers seem to prefer. 😉
-- Gianluca Sartori
October 6, 2011 at 2:11 am
They are using the query into web focus application to generate report purpose, when they click the generate button , it ran and get data from table it takes some time , while that time all connction went suspened status, after that staying in suspended , dont get back normal.
Any thing coding writing to kill connection?
Please advice me.
October 6, 2011 at 2:49 am
To kill a connection you just need to use the KILL statment with processid that you need to Kill.
though I agree with the others that this approach seems a bit flawed as most front end reporting systems do allow for multiple datasets on the drill down. Web focus does also have the ability to do this. Perhaps a better approach would be to get the report developers to develop more efficent reports.
At the end of the day if you are returning a large number of rows it is going to take time as you are moving so much data.
November 16, 2011 at 7:18 am
Hi Bro
Its never in a chance or requirement where u may need 1m records even if so then those many records are never been viewd.Just make sure what you actually need from them that 1m records and put where caluse to get filter that will reduce your time and records as well
And as far your question for performance even for those records i would suggest you to go for pageing query which will have all your records but still for a time being you can view select range of records.
Thanks & Regards
Syed Sami Ur Rehman
SQL-Server (Developer)
Hyderabad
Email-sami.sqldba@gmail.com
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply