Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««12

Improve SIMPLE SELECT to retun 2 LAC Rows faster? Expand / Collapse
Author
Message
Posted Wednesday, December 18, 2013 7:45 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 2:34 PM
Points: 12,916, Visits: 32,077
bharat sethi (12/18/2013)
Hi! The data is not going to a web page. It's a SSIS package which process this task and then provide this data to sucessor tasks or other processes. there is no image/text columns (pl. refer previous replies for columns list) and H/w had a gigbit LAN only.

The query is Select <column names> from <Temp.TableName>.



ok, nine posts into this thread, and now you tell us this is part of an SSIS package? don't you think you might have been relevant to the question at hand? the first post implies it's slow, but without identifying much more than that.

if it's in an SSIS package, why does it have to be fast? doesn't the data simply need to be processed, regardless of whether it takes one second or one minute?

help us help you.

remember, we are not standing in your cubicle, looking over your shoulder; we only know what you post here. A lot of us have an incredible amount of experience, and volunteer our time to help others.
if you can provide details, we can probably help, but so far, you haven't done your part on providing anything other than a textual description of the issue.
so far, I've understood that you are returning 200K rows of data, and it's a pretty large dataset, and it takes "more than 3 Minutes to list all records."

how is it you see it takes 3 minutes to "list" them?
in ssms by running the stored procedure call?

for any of us to diagnose anything, we need concrete things like a description of the process, and what is actually going wrong; any specific error numbers raised; stored procedure definitions, table definitions, indexes on the relevant tables, actual execution plans; anything that might identify to us the pain points you might be experiencing.


the proc definition might be relevant for example, if you were using a linked server, or any of a number of other things i can think of performance wise.
Hope you can help us help you!


Lowell

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son
Post #1524137
Posted Thursday, December 26, 2013 2:15 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, September 4, 2014 2:30 PM
Points: 39, Visits: 94
Without looking at details/specifics,
you can try to get a 'blind' performance gain by using @Table table type variable instead of
#temporary table. You can gain performance improvement to some extent by avoiding writing/reading to/form disk
because table type variable is processed in memory. But with a large number of rows it may present another problem,
depending on how much available memory your server has and you do need to look ant the query exec plan, no doubt.



Voldemar
likes to play chess
Post #1526052
Posted Thursday, December 26, 2013 2:21 PM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Yesterday @ 7:22 PM
Points: 3,637, Visits: 7,935
vladisaev@hotmail.com (12/26/2013)
Without looking at details/specifics,
you can try to get a 'blind' performance gain by using @Table table type variable instead of
#temporary table. You can gain performance improvement to some extent by avoiding writing/reading to/form disk
because table type variable is processed in memory. But with a large number of rows it may present another problem,
depending on how much available memory your server has and you do need to look ant the query exec plan, no doubt.

Note that temp tables can reside in memory and table variables can be written to disk if needed.
http://sqlserverplanet.com/tsql/yet-another-temp-tables-vs-table-variables-article



Luis C.
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

Forum Etiquette: How to post data/code on a forum to get the best help
Post #1526055
« Prev Topic | Next Topic »

Add to briefcase ««12

Permissions Expand / Collapse