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 ««1234»»»

Best way to select a large amount of data? Expand / Collapse
Author
Message
Posted Friday, August 20, 2010 9:40 AM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Tuesday, November 26, 2013 8:40 AM
Points: 316, Visits: 908
Can you explain a little more in detail exactly what you are doing ?

You say the report is a T-SQL program.

Exactly what does that mean ?

Do you have a stored procedure that writes 5 million rows to a temporary table ?

Do you run a stored procedure from SSMS, return 5 million rows and then try to write them to a file from SSMS ?

Or what are you really doing ?
Post #972633
Posted Friday, August 20, 2010 9:55 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, July 8, 2014 4:40 PM
Points: 136, Visits: 530
Ok, the T-sql program loads all the needed data into a table, then another process use that data for a webpage ( believeme all of that data is needed) that said
Again
What is the best way to retrieve that much data without uning a single query that block my main table for more that an hour ( I can't use nolock since I can't affort to have dirty reads) and still remain optimal?
Post #972651
Posted Friday, August 20, 2010 10:16 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: Administrators
Last Login: Today @ 11:46 AM
Points: 33,098, Visits: 15,205
There isn't a good way here. You need to move that much data, and it will block things.

I still question this is really needed. No one can use 5mm rows at once, and if you are summarizing, why aren't you using SQL to do some summaries? Display smaller chunks of data, or pull back smaller chunks at a time.







Follow me on Twitter: @way0utwest

Forum Etiquette: How to post data/code on a forum to get the best help
Post #972668
Posted Friday, August 20, 2010 10:28 AM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Tuesday, November 26, 2013 8:40 AM
Points: 316, Visits: 908
ricardo_chicas (8/20/2010)
Ok, the T-sql program loads all the needed data into a table, then another process use that data for a webpage ( believeme all of that data is needed) that said
Again
What is the best way to retrieve that much data without uning a single query that block my main table for more that an hour ( I can't use nolock since I can't affort to have dirty reads) and still remain optimal?


I'm trying to help you here, but you are really providing too little information. Please be more detailed.

My understanding so far is that you have one large table with 124 million rows

you then use a T-SQL script to move 5 million of those rows to a secondary table.

An external program then reads that secondary table and sends it over a network link to an end user.

You then say that something blocks the main table for over an hour. Which table is being blocked ?

Is the problem in the T-SQL script that moves data between the two tables, or is the problem in the external program that tries to send data over a slow network ?

Right now it sounds like the external program is the problem.

If that is the case a suggestion could be to let the external program read all the data from the database as quickly as possible end just save the data to a temporary file on disk.

Then the the external program can read the data from the temporary file and send it to the client.

Have I understood your problem ?
Post #972683
Posted Friday, August 20, 2010 11:06 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, July 8, 2014 4:40 PM
Points: 136, Visits: 530
Ok lets do this, forget about the original problem
what I really want to know is for a suggestion on what is the best way to retrieve large amount of data, without blocking tables, without using no log, using a select, when I use oracle I was able to do something like that using a bulk select ( I set the select to retrieve chunks of 10000 rows), I am not sure if that kind of stuff works in sql server, but i want something like that

Thank you
Post #972702
Posted Friday, August 20, 2010 11:32 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 11:52 AM
Points: 12,890, Visits: 31,847
ricardo_chicas (8/20/2010)
Ok lets do this, forget about the original problem
what I really want to know is for a suggestion on what is the best way to retrieve large amount of data, without blocking tables, without using no log, using a select, when I use oracle I was able to do something like that using a bulk select ( I set the select to retrieve chunks of 10000 rows), I am not sure if that kind of stuff works in sql server, but i want something like that

Thank you


as everyone else is telling you; 5 million rows takes time to move.

the right thing to do is not move the data at all, and look at what the process is doing with the data.
whatever that process is, it can be fixed and cleaned up so it doesn't take so long and doesn't block the server.

grabbing 5 million rows in 10K bites is not going to do anything faster for you; you need to tackle it a different way; for example, how often is that 5million rows downloaded ? more than once? why is it not cached?

As someone else already said, the secondary process that does stuff to the 5 million rows should be looked at so that a single summarizing statement that is executed on SQL server is used to replace that process.


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 #972720
Posted Friday, August 20, 2010 12:03 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 11:28 AM
Points: 36,786, Visits: 31,244
ricardo_chicas (8/20/2010)
Ok lets do this, forget about the original problem
what I really want to know is for a suggestion on what is the best way to retrieve large amount of data, without blocking tables, without using no log, using a select, when I use oracle I was able to do something like that using a bulk select ( I set the select to retrieve chunks of 10000 rows), I am not sure if that kind of stuff works in sql server, but i want something like that

Thank you


We're just guessing here. Post your current query and post the information requested by the article at the second link in my signature line below. Thanks.


--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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #972734
Posted Friday, August 20, 2010 12:50 PM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Tuesday, November 26, 2013 8:40 AM
Points: 316, Visits: 908
ricardo_chicas (8/20/2010)
Ok lets do this, forget about the original problem
what I really want to know is for a suggestion on what is the best way to retrieve large amount of data, without blocking tables, without using no log, using a select, when I use oracle I was able to do something like that using a bulk select ( I set the select to retrieve chunks of 10000 rows), I am not sure if that kind of stuff works in sql server, but i want something like that

Thank you


Well if you ask the question like that the only possible answer is that you dont need to do anything special. The application just needs to issue the select query and then consume the returned data as fast as it can.

There will be blocking - another application will not be able to insert data into the table until the application has finished reading all the data.

To make blocking shorter you have to minimize the time used to consume the returned data. You application should just read the data as quicklu as possible without waiting for anything else.

To avoid blocking completely you would have to use snapshot isolation (check BOL) or use the NOLOCK hint in the select.

Your real problem is probably that your application is not reading data fast enough from the SQL server connection.

I have no idea what an Oracle BULK SELECT does.
Post #972761
Posted Friday, August 20, 2010 2:25 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, May 14, 2012 3:42 AM
Points: 14, Visits: 101
Here is a question, if you select 5mm or selecting 10Kx500 won't the time it takes to do the select equal in any case?
If so what is the point in "BULK" selecting?
I would also just opt for hinting or isolation then lastly check to see what the external program is doing.
Post #972823
Posted Saturday, August 21, 2010 8:54 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 11:28 AM
Points: 36,786, Visits: 31,244
Stefan_G (8/20/2010)
There will be blocking - another application will not be able to insert data into the table until the application has finished reading all the data.


It depends. It depends on what the indexes are (especially the clustered index for inserts) and what the scope/range of data is being selected. Tables are not necessarily totally locked just because someone selects a large number of rows. 5M out of 120M is only a little over 4% of the table and can easily "survive" on page locks alone.


--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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #972950
« Prev Topic | Next Topic »

Add to briefcase ««1234»»»

Permissions Expand / Collapse