Sign in to follow this  

[web] Help with SQL Query (optimization)

This topic is 4198 days old which is more than the 365 day threshold we allow for new replies. Please post a new topic.

If you intended to correct an error in the post then please contact us.

Recommended Posts

Basically I'm trying to grab 2 fields from one table and 1 field from another, we will call them Table1.Field1, Table1.Field2, and Table2.Field1..(so i can explain it a bit better) and I'm supplied with the Primary key of Table2, and the Foreign Key of Table1 (same value) here is what I have now
SELECT Table1.Field1, Table1.Field2, Table2.Field1 FROM Table2 INNER JOIN Table1 
ON Table2.PrimaryKey = Table1.ForeignKey WHERE Table2.PrimaryKey = " + myVariable
Is that how the query should be done? and is that as fast I can make it? I'm asking because I have a page that is taking a while to load sometimes, I don't know if it's this query or another one...I ask about this one because I'm not sure how well INNER JOIN works..Should I be using that at all? Thanks ArchG

Share this post


Link to post
Share on other sites
It should make no difference whether you use the INNER JOIN keyword, or simply place all criteria in the WHERE clause.

If "The page takes a while to load sometimes", then you should benchmark the application in more detail.

Obviously any profiling should take place on an otherwise idle development server, NOT running the web browser locally (web browser + server fight for CPU time).

If you can reproduce the problem with a specific case on your otherwise idle dev server, then you can do some profiling to determine exactly what the cause is.

The most obvious culprit would be the fact that the appropriate indexes haven't been put on the columns you're joining.

Anything which is a primary key (the WHOLE key) will have an index placed on it automatically. The same is not true for foreign keys and each foreign key being used SHOULD usually have an index.

Try the benchmark with & without the index (selects only, make sure you have at least 100k rows in the table you're joining and the join matches relatively few of them (e.g. 100)) - you'll be amazed at the difference.

Of course one thing that a lot of people don't realise, is that indexes actually make INSERTs and UPDATEs slower.

It is possible to have too many indexes but difficult, so better to have lots than few.

Mark

Share this post


Link to post
Share on other sites

This topic is 4198 days old which is more than the 365 day threshold we allow for new replies. Please post a new topic.

If you intended to correct an error in the post then please contact us.

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now

Sign in to follow this