Refining sequential Views at SQL Server 2005
Hi there!
I am developing a rather complex database that involves many Views with many function-related fields involved. Even using the Database Tuning Advisor, I realize that some of these Views take aprox. 10 sec to process (without taking into account data presentation) and I need to use these same time-demanding Views as "virtual tables" to future Views, what seems to involve future increase in the time to process it all.
My idea: apply something analogous to Access´ Make-Table queries, to be able to make a table from the "first time-demanding View" and, from then on, to make other Views related to this table, not to the View-based anymore.
I know I will have to put this "make-table query" analogous to work with manual interference, when the user wants to "refresh" the final calculated data. But this is not a problem here. We would need to refresh the whole thing say 2 per bussiness day, approximately.
Questions:
01. Do you feel my approach is reasonable ?
02. If so, what is the "make-table query" analogous in SQL Server 2005 techniques?
If not, what would you do to by-pass this chain-time-consuming phenomenum ?
Thanks a lot,
fskilnik.
I am developing a rather complex database that involves many Views with many function-related fields involved. Even using the Database Tuning Advisor, I realize that some of these Views take aprox. 10 sec to process (without taking into account data presentation) and I need to use these same time-demanding Views as "virtual tables" to future Views, what seems to involve future increase in the time to process it all.
My idea: apply something analogous to Access´ Make-Table queries, to be able to make a table from the "first time-demanding View" and, from then on, to make other Views related to this table, not to the View-based anymore.
I know I will have to put this "make-table query" analogous to work with manual interference, when the user wants to "refresh" the final calculated data. But this is not a problem here. We would need to refresh the whole thing say 2 per bussiness day, approximately.
Questions:
01. Do you feel my approach is reasonable ?
02. If so, what is the "make-table query" analogous in SQL Server 2005 techniques?
If not, what would you do to by-pass this chain-time-consuming phenomenum ?
Thanks a lot,
fskilnik.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Uau... thanks a lot for the nice comments, ralmada and M@ !!
ralmada: I guess you HAD the same ideas/doubts I HAD... I also thought about stored procedures before coming here to ask and... well, have a look at M@ explanations! They made me write HAD instead of HAVE... ;)
M@ : First of all, thanks for joining. I hope I don´t get "addicted" to your great remarks/expertise as I got with EE "harfang"´s profound knowledge of MS Access... Well... I hope I do. :)
>> I know the common assumption is that when you run a SELECT against a view, the view runs and then the select executes against it, making their times additive, but that isn't how SQL really works. ... So you can't really go by the time the view takes to run as your benchmark.
Marvellous. Yes, I was certainly "looking for a storm after the first rainy day has come"... The first thing then is NOT to draw conclusions related to my future Views performances! I won´t.
>> it may be putting a bandage on something that needs to be fixed.
Great. I also thought it as a (perhaps even good) "last-resource" solution, that´s why I put the question in the first place...
>> the indexing was as good as it could be
The problem here is that Views based on Views cannot have indexes, if I understood it right. Therefore even if I would like to put them (perhaps unclustered ones) during the whole "path", I could not... right ? (Question 01)
>> your table relationships are as good as possible
Question 02: I did relate them as far as Primary Key - corresponding Foreign Key ´s are concerned and I still CAN put many more, as far as (necessarily equal) "TransactionDates" fields, for instance, are concerned. Do you suggest me to do that ?
>> The database tuning advisor is OK, but sometimes it really misses the mark.
Funny thing. It advised me much LESS than I suspected, really...
Question 03: the suggestions are never/ever/sometimes implemented by the system itself or do I have to manually input them? I ask it because the single suggestion the tuning advisor gave me was implemented when I would include it and some others, that the tuning advisor didn´t advice, I don´t thing I created before using it... am I getting crazy??
I really thank you for your time, M@. Thanks a lot, Fábio.
ralmada: I guess you HAD the same ideas/doubts I HAD... I also thought about stored procedures before coming here to ask and... well, have a look at M@ explanations! They made me write HAD instead of HAVE... ;)
M@ : First of all, thanks for joining. I hope I don´t get "addicted" to your great remarks/expertise as I got with EE "harfang"´s profound knowledge of MS Access... Well... I hope I do. :)
>> I know the common assumption is that when you run a SELECT against a view, the view runs and then the select executes against it, making their times additive, but that isn't how SQL really works. ... So you can't really go by the time the view takes to run as your benchmark.
Marvellous. Yes, I was certainly "looking for a storm after the first rainy day has come"... The first thing then is NOT to draw conclusions related to my future Views performances! I won´t.
>> it may be putting a bandage on something that needs to be fixed.
Great. I also thought it as a (perhaps even good) "last-resource" solution, that´s why I put the question in the first place...
>> the indexing was as good as it could be
The problem here is that Views based on Views cannot have indexes, if I understood it right. Therefore even if I would like to put them (perhaps unclustered ones) during the whole "path", I could not... right ? (Question 01)
>> your table relationships are as good as possible
Question 02: I did relate them as far as Primary Key - corresponding Foreign Key ´s are concerned and I still CAN put many more, as far as (necessarily equal) "TransactionDates" fields, for instance, are concerned. Do you suggest me to do that ?
>> The database tuning advisor is OK, but sometimes it really misses the mark.
Funny thing. It advised me much LESS than I suspected, really...
Question 03: the suggestions are never/ever/sometimes implemented by the system itself or do I have to manually input them? I ask it because the single suggestion the tuning advisor gave me was implemented when I would include it and some others, that the tuning advisor didn´t advice, I don´t thing I created before using it... am I getting crazy??
I really thank you for your time, M@. Thanks a lot, Fábio.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Excellent, M@ !
[Points Increased to 500. Yours, sure.]
Best Regards,
Fábio.
[Points Increased to 500. Yours, sure.]
Best Regards,
Fábio.
ASKER
P.S.: to be able to consider the "Assisted" option to ralmada, I guess it´s fair to put 20 points for him, ok?! Thanks!
Sure. However you want to do it is fine. I just like helping folks...the points are just bonus.
M@
M@
Thanks for the points too!!!
I must admit that M@ solution is superb.
I must admit that M@ solution is superb.
ASKER
Nice!! You two were great.
Cheers,
Fábio.
Cheers,
Fábio.
I know the common assumption is that when you run a SELECT against a view, the view runs and then the select executes against it, making their times additive, but that isn't how SQL really works.
Keep in mind that if you are going to be using the views to feed stored procedures or select statements, there will be criteria that will greatly reduce the amount of processing the SQL has to do. For example, if a query selects only the records for a certain date, SQL can quickly eliminate all records that don't match that date. So you can't really go by the time the view takes to run as your benchmark. A much more useful test would be to run some queries that apply what will be typical criteria in the WHERE clause and se how well it performs. For example:
SELECT COUNT(1)
FROM MyView
WHERE CustomerID = 12345
SELECT COUNT(1)
FROM MyView
WHERE InvoiceDate BETWEEN '01/01/2006' AND 01/31/2006'
These tests will give you better real-world times.
I don't think your idea is neccessarily bad, but I think it may be putting a bandage on something that needs to be fixed. I would verify that the indexing was as good as it could be and your table relationships are as good as possible. The database tuning advisor is OK, but sometimes it really misses the mark.
Try those things and see if your performance is improved. If not, it may be time to look at working from temporary tables, but as I mentioned that should be your last resort.
M@