Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Improve SIMPLE SELECT to retun 2 LAC Rows faster?


Improve SIMPLE SELECT to retun 2 LAC Rows faster?

Author
Message
Lowell
Lowell
SSChampion
SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)

Group: General Forum Members
Points: 14957 Visits: 38960
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

--
help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

VoldemarG
VoldemarG
Valued Member
Valued Member (69 reputation)Valued Member (69 reputation)Valued Member (69 reputation)Valued Member (69 reputation)Valued Member (69 reputation)Valued Member (69 reputation)Valued Member (69 reputation)Valued Member (69 reputation)

Group: General Forum Members
Points: 69 Visits: 190
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
Luis Cazares
Luis Cazares
SSCrazy Eights
SSCrazy Eights (8.5K reputation)SSCrazy Eights (8.5K reputation)SSCrazy Eights (8.5K reputation)SSCrazy Eights (8.5K reputation)SSCrazy Eights (8.5K reputation)SSCrazy Eights (8.5K reputation)SSCrazy Eights (8.5K reputation)SSCrazy Eights (8.5K reputation)

Group: General Forum Members
Points: 8536 Visits: 18131
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.
General Disclaimer:
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?


How to post data/code on a forum to get the best help: Option 1 / Option 2
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search