CODEWITHSUNDEEP
sql-servervisual-studio-2010sql-server-2008datagridview
Hossein
Hossein

Reputation: 26004

How to Speed Up select statements in SQL Server

I have a very simple select Statement, as simple as :

SELECT * FROM vContacts

vContacts is a view created out of tblContacts Table has only 218 records in it.
When I use the aforementioned select query it takes the whole 9.89 seconds! to fill the DataGridView!
I Use View for the DataGridview on my form since the names should be in Farsi and not Englishand all fields are present and there is nothing else).
When I try to execute the view statement inside Visual Studios 2010 built-in SQL Designer , it is amazingly fast! but on my windows form it takes 10 seconds!!

To be more precise :

This is the View statement : 

SELECT ID, Name, Tel, Mobile, Address, Description
FROM  dbo.tblTel

I use this method for retrieving the whole table 

public static DataTable GetTable(string tableName, string conncetionString, bool structureOnly = false)
        {
            DataTable table = new DataTable();
            string query = structureOnly ? string.Format("SELECT * FROM {0} WHERE 1=0", tableName) : string.Format("SELECT * FROM {0} ", tableName);
            SqlConnection connection = new SqlConnection(conncetionString);
            connection.Open();
            SqlCommand command = new SqlCommand(query, connection);
            SqlDataAdapter reader = new SqlDataAdapter(command);
            reader.Fill(table);
            connection.Close();
            return table;
        }

and use it like:

dataGridView.DataSource = DBAPI.GetTable(vContacts,DBAPI.ConnectionString);

Upvotes: 2

Views: 512

Answers (2)

Hossein
Hossein

Reputation: 26004

I Found The problem.It was caused by the DataGridView Component.
DotnetBars DataGridViewX has some terrible issues in this regard , Since when i tried using the ordinary DataGridView it took less than a second ( couple of milliseconds actually) to show ALL records while the DotnetBar's DataGridViewX took a whole 10 seconds to show only 218 records !!! So as a rule of thumb, I should never ever use 3rd Party controls specially the DotnetBars!

Upvotes: 1

alzaimar
alzaimar

Reputation: 4622

If you execute a query in the SQL Designer, you will see the first rows as soon as they are calculated, although the query itself may take hours to finish.

When you execute your C# code, the grid does not get repainted (i.e. shows the values) until the query is finished.

I am not quite sure about the built in SQL designer, but if you execute a query in SSMS, you see the total execution time in the lower right corner. Also, you will see results allthough the timer is still counting up and the Cancel-button (with the red dot) is active.

Upvotes: 1

Related Questions