January 24, 2003 at 4:52 pm
Hi,
I just want to be sure this technique is not going to cause me grief when my database has millions of rows.
While extracting data from table B I want summary data from table A (a column in A is a FK to B's PK - there is an index on the FK column in A). I have a view for table A that calculates summary data based upon the FK. The SQL on table B extracts a single row has an equijoin to table A to extract the summary data for the single record it is retrieving.
My question relates to what SQL 2000 does with table A - does it 'know' it only wants a single record and therefore only calculates summary data for one FK or does it summarise the entire table then picks the record it wants?
The execution plan indicates that it only gets records for the single FK from table A. I just would like one of you gurus to confirm that this is the case before I go to far down this road.
Now a related question if I may - suppose I extracted a group of rows from table B rarher than one. Would this cause the view on table A to summarise the entire table or would it just select the ones matching the selected range of FK's from table B?
Cheers, Peter
January 24, 2003 at 5:06 pm
I think the result may depend on how you will be using the view. If it is a part of a query that is not very trivial, I would not use a view but join the tables directly in the proper order of clauses:
FROM tbl_1
JOIN tbl_2
ON tbl_2.col_1 = @abc
AND tbl_1.fk = tbl_2.pk
In fact, I always avoid views just to stay in the predictable world. I realize it is a concervative approach. If you deal with millions of records, I would not recommend messing around with views anyway.
January 24, 2003 at 11:59 pm
Thanks for the feedback.
I don't have much experience in writing SQL - my background is Access and linking queries is common place. A view (to me) looked like a an Access query and was 'easy to understand and use'. From what you say I shouldn't be viewing (bad pun - sorry) them that way and should try to avoid them.
Joining tables in SQL is kinda ok but a join on a summarised table seemed a bit heavy duty for me. I will check out the Books Online for guidence. Is there any other good source of sample SQL for the newbie?
Cheers, Peter
January 25, 2003 at 5:27 am
For a single query, I'd skip the view too. If you're going to be the select in a lot of places, putting into a view makes sense. If you really chain a lot of views you make see issues, but deal with those when they happen. After all, its easy enough to replace the view in the query if you need to!
Andy
January 25, 2003 at 4:26 pm
Hi Andy,
Thanks for the comment. But can you expand - what is a "see issue"?
Also, can you comment on my original question - will SQL Server perform a complete scan of table A to return the summary for one item?
Cheers, Peter
January 25, 2003 at 6:20 pm
The best way to see what SQL is doing is to profile it and/or examine the query plan. Query plan is easy to look at in Query Analyzer and it does have a basic profiling option built in that is 'good enough' for most cases when you're checking query performance.
As far as issues, there are times when regardless of the sql you use, SQL doesn't generate the best query plan. Certainly there are cases where bad statistics or bad indexes contribute, but there are cases too where you can 'trick' SQL into being more effective. For the most part those are special cases and even in those decent hardware masks the impact. I guess the bottom line is that you should write code that is easy to read and works. Then, IF you find it doesn't perform as well as you need, investigate and tune until it does. Views are often an elegant solution and simplify code, but like any feature you can use it too much or too little!
Andy
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply