SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2012 Forums
 SQL Server Administration (2012)
 Large Data Retrieval via Visual Studio 2010
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

denni50
Starting Member

8 Posts

Posted - 04/29/2014 :  16:19:11  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

USA
6062 Posts

Posted - 04/29/2014 :  17:39:27  Show Profile  Reply with Quote
>>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
Go to Top of Page

jackv
Flowing Fount of Yak Knowledge

United Kingdom
2076 Posts

Posted - 04/30/2014 :  01:39:25  Show Profile  Visit jackv's Homepage  Reply with Quote
As a test , are you able to run SSMS on client and server side , compare the times?

Jack Vamvas
--------------------
http://www.sqlserver-dba.com
Go to Top of Page

denni50
Starting Member

8 Posts

Posted - 04/30/2014 :  08:31:23  Show Profile  Reply with Quote
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.





Go to Top of Page

TG
Flowing Fount of Yak Knowledge

USA
6062 Posts

Posted - 04/30/2014 :  10:20:49  Show Profile  Reply with Quote
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
Go to Top of Page

denni50
Starting Member

8 Posts

Posted - 04/30/2014 :  10:54:40  Show Profile  Reply with Quote
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.
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.09 seconds. Powered By: Snitz Forums 2000