asked on

Optimize SQL JOIN statements for DataGrid

I'm using VB6 against an SQL Server database.
I'm using an ADO recordset to populate a datagrid control.
I noticed that the grid performs better with different sql select strings.

I need help in writing the best sql select string to populate my grid.
Mostly, on this select from two tables with a common id:
This one performs slow:
"SELECT * FROM Cuts, Appropriations WHERE Cuts.AppropId = Appropriations.Id"

While this string is faster:
Select * from cuts join Appropriations on cuts.appropid = Appropriations.id

Can anyone suggest the best way to write sql statements which draw data from two or more tables?

Thanks
John

ASKER CERTIFIED SOLUTION

Joe_Griffith

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

trkcorp

<< Select * from cuts join Appropriations on cuts.appropid = Appropriations.id >>

IS better and the only way I know to improve on this is to PERHAPS create an index the column cuts.appropid, if one does not exist. This may or may not help, depending on the data... Is Appropriations.id a one-to-many relationship to cuts.appropid or vice versa or one-to-one... These things are important to know.

Joe_Griffith

I'm actually surprised that selecting specific items had an effect. I thought I read somewhere that SELECT * was faster than selecting individual items. Of course if the table contained many fields and you only need a few it would surely be faster to select specific ones.

trkcorp

It is always faster to retrieve less than to retrieve more... I would suspect that just stating column names alone saves the overhead of SQL server saying to itself, "Oh, he wants it all, what are they now?" and having to build a list...