Fastest way to get the Data from Sql Server DB

Question

4.00/5 (1 vote)

See more:

Hi,

We have the billions of records in the SQL Server DB, and I need to read that data using my Windows Application.

When I execute the Script (Inner Joining 2 tables) in the SQL Server I am able to see the result in 40 secs, But when run my windows application it is taking more than one min.

here in the Code I written in my app:

C#

private DataSet GetData(string query, string dbName)
        {
            try
            {
                DataSet ds = new DataSet();
                con = new SqlConnection(GetConnectionString(dbName));
                con.Open();
                da = new SqlDataAdapter(query, con);
                con.Close();
                
                da.Fill(ds);
                return ds;
            }
            catch (Exception ex)
            {
                MessageBox.Show("Error: " + ex.Message, "Excess Static Data", MessageBoxButton.OK, MessageBoxImage.Error);
                return null;
            }
        }

Dataset is taking much time to get the data.

Is there any fastest way to get the data less than 10sec.

Regards,
Murali.

Posted 15-Apr-13 3:49am

Murali Krishna Babu

Add a Solution

Comments

Zoltán Zörgő 15-Apr-13 9:56am

You can't do this "in sql server". You do it in SQL Management studio, with a row limit set to 1000. That is why it is quicker. And the result set size does not really depend on the number of records in the database, but the query itself. You can have a query with empty result set or one with record count many times the number of records you have.

Prasad Khandekar 15-Apr-13 10:28am

If you are using SQL Server 2012 then you can use server side pagination. Here is the link (http://msdn.microsoft.com/en-us/library/ms188385(v=sql.110).aspx)

Mike Meinz 15-Apr-13 14:27pm

You didn't share your DDL or your DML. That makes it difficult to give a good answer.

Do your tables have indexes on the columns used in the Join? If not, you should add some.

Does the query result in millions of rows to be sent from the SQL Server to the Windows PC? If so, you should expect it to take a long time because the data has to move across the network.

Ankur^\m/ 16-Apr-13 1:28am

[moved from direct reply to the question]
Murali Krishna Babu - 12 mins ago
Hi, Thanks for the reply, My Query is like this : "Select a.DataID,a.DataPointID,c.Name,a.ValueEndOf10MinuteInterval,a.ValueEndOf10MinuteIntervalDelta,b.TimeStampLocalSystem as TimeStampLocalSystem,b.TimeStampUTCSystem as TimeStampUTCSystem from StatisticData a WITH(NOLOCK) Inner Join Data b on a.DataID = b.DataID Inner Join SCADAConfiguration.dbo.SystemDescription c on b.SystemNumber = c.SystemNumber and a.DatapointId = 2727 and b.SystemNumber =" + kvp.Key + " and b.TimeStampLocalSystem >= '" + stDate + "' and b.TimeStampLocalSystem < '" + enDate + "' order by b.TimeStampLocalSystem" When I execute this in SQL Management studio I am getting the result within 40 Secs, but when I tried to execute the same query via Windows Application by using the SQLDataAdapter, it is taking more than 50 secs to get the result. Any possibilities to minimize the transaction time..? --Regards, Murali.

Ankur^\m/ 16-Apr-13 1:34am

There shouldn't be a difference of 10 secs. There could be something wrong somewhere else as well. Can you record the time in your GetData() method. Store current time in a TimeSpan object on the very first line and then again after dataset is filled. Subtract the time and let me know the result.
Also mind you that SQL caches query plan. So first execution is always slower than the next ones.

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

babu saravanan · Answer 1 · 2013-04-15T18:42:00

Solution 1

Try this:

instead of using dataSet you can use dataReader, it will be efficient

Posted 15-Apr-13 18:42pm

babu saravanan

Comments

Murali Krishna Babu 16-Apr-13 1:23am

Hi Saravanan,

Thanks for the reply, As per my knowledge, datareader is using connected architecture, Dataset is using Disconnected architecture. And in my application I am using TPL concept, If I use datareader, I will get an exception like "Connection already OPEN".

Is there any other fastest way to get it work?

--Regards,
Murali.

Ankur^\m/ 16-Apr-13 1:26am

It certainly will be faster but DataReader can only be used to retrieve read-only, forward-only stream of data from a database. So if it's only 'select' to display as is, it is fine. There are lots of benefits that disconnectde architecture has.