Large Data Retrieval via Visual Studio 2010

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2012 Forums

SQL Server Administration (2012)

Large Data Retrieval via Visual Studio 2010

Author

Topic

denni50
Starting Member

8 Posts

Posted - 2014-04-29 : 16:19:11

Hi Everyone
not sure if this is the correct place to ask this question.

we have a new SQL Server 2012 db that resides on a Windows 2008 Server. We import large oracle tables into it for special projects.

I installed Visual Studio 2010 on a user workstation and connected to the SQL Server db. the table contains 3.9 million records.

when I query the data on the server side it populates in 3 minutes, when I query the data from Visual Studio it takes 43 minutes.

is there some bulk collect feature I can setup from the user side that will retrieve data faster from large tables.

thanks for any tips and suggestions.

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2014-04-29 : 17:39:27

>>when I query the data on the server side it populates in 3 minutes, when I query the data from Visual Studio it takes 43 minutes.
what are you "populating" server side?
Of course pulling 3.9 mil rows across the network and loading into some object will take considerably longer than populating a different table on the sql server.
When you "retrieve data to the user side" to what are you pulling the data to? a file? 3.9 mil rows is way too many to scroll through so what is the objective?

Be One with the Optimizer
TG

jackv
Master Smack Fu Yak Hacker

2179 Posts

Posted - 2014-04-30 : 01:39:25

As a test , are you able to run SSMS on client and server side , compare the times?

Jack Vamvas
--------------------
http://www.sqlserver-dba.com

denni50
Starting Member

8 Posts

Posted - 2014-04-30 : 08:31:23

thanks TG and jackv for responding, let me provide a little background.

I work in Direct Marketing and we process Mailing data for Donor Campaigns. users have several steps they do in prepping the data for Mailing. the first step is to dedupe(remove duplicate donor records) from the table, that is why all 3.9 million records are loaded, once the duplicates are removed they then divide the table into groups based on historical giving and spool out the groups for Printing and Mailing.

in answer to jackv when I queried the records on the server side all 3.9 million records loaded in 3:02 ( 3 minutes 2 seconds), when I queried on the client side it took 43:09. the client connects to the server with no problems, retrieving the data in reasonable time is the problem. thanks.

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2014-04-30 : 10:20:49

The background is interesting but not very helpful. Please describe the process(es) the you are comparing.
ie:
server side:
From sql server management studio "select * from ..." to a results pane in grid mode. (3:02)
Are you connected to the sql server from your workstation via SSMS? or are you physically logged into the sql server as if it is your local workstation?

client side
same process? why does your topic subject mention "...via Visual Studio 2010"
what are you doing specifically? perhaps using System.Data.SqlClient classes like SqlConnection, SqlCommand, and SqlDataReader to connect, select, and read through the 3.9 mil rows?

Rather than me guessing at all the possibilities can you please describe exactly what you're doing from each environment. Also approximately how many bytes of data comprise one of these 3.9 mil rows?

>>is there some bulk collect feature I can setup from the user side that will retrieve data faster from large tables.
Take a look at BULK COPY.

Be One with the Optimizer
TG

denni50
Starting Member

8 Posts

Posted - 2014-04-30 : 10:54:40

hi TG

when I run the query from the server side I am directly connected to the server by remote control.

when I run the query from the client workstation I am connect to the Sql Server db through Visual Studio 2010 using the Server Explorer>Data Connection.

if there is another connection method from the client side I can use that is more economical than I would certainly try that.

here's row size:
SELECT OBJECT_NAME(syscolumns.[id]) AS [Table Name] ,

SUM(syscolumns.length) AS [Row Size]

FROM syscolumns

JOIN sysobjects ON syscolumns.[id] = sysobjects.[id]

WHERE sysobjects.xtype = 'U'

GROUP BY OBJECT_NAME(syscolumns.[id])

MAILSWA 1810
sysdiagrams 267

thanks.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources