• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 218
  • Last Modified:

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

0
jtrapat1
Asked:
jtrapat1
  • 2
  • 2
1 Solution
 
Joe_GriffithCommented:
Do you really need everything from both tables?  You might try just selecting the specific items you need.
0
 
trkcorpCommented:
<< 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.
0
 
Joe_GriffithCommented:
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.
0
 
trkcorpCommented:
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...
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now