June 11, 2008 at 1:29 am
Hi everyone,
Using VIEW for accessing data from DB takes more time(14 minutes) while same amount of data from TABLE takes just 18 sec..
will anyone justify PLZ...
FOR details see below
Case1) For VIEW:
------------------------------------------------------------
I have 12 tables (lets assume each table for separate device with devId=1 to 12).
eg. tab_dev1
sTime sVal sAvg
10/21/2005 6:01:00 PM2510
10/21/2005 6:02:00 PM2012
such 10000 records are inserted into each table.
----------------------------------------------------------------
Also i have created one VIEW for each table(total 12 views) by adding devId to it as..........
ALTER VIEW [dbo].[vw_samples] AS
SELECT dbo.Config.devID, dbo.Samples.*
FROM dbo.Config CROSS JOIN dbo.Samples
----------------------------------------------------------------
finaly all views are unioned to make one view 'vw_all_samples' as
ALTER VIEW [dbo].[vw_all_samples] AS
SELECT devId,sTime, sValue, sAvg
FROM btUnit01.dbo.vw_Samples AS Samples01
UNION ALL
SELECT devId,sTime, sValue, sAvg
FROM btUnit02.dbo.vw_Samples as Samples02
UNION ALL........upto 12 tables
--------------------------------------------------------------
Case2) FOR TABLE:
-----------------------------------------------------
I have made single table as
devId sTime sVal sAvg
1 10/21/2005 6:01:00 PM2510
6 10/21/2005 6:02:00 PM2012
...etc....total 12*10000=120000values in single table...
Case1 takes 14 minutes for fetching 1440 values
Case2 takes just 18 seconds...
WHYYYYYY ???????
June 11, 2008 at 6:23 am
The view-based query might be performing "as expected" for what it is. The view has some huge obstacles to overcome: the views are using cross joins -- joins do not get much worse than that; 12 of these cross joins are unioned together -- this takes a slow process and makes it 12 times slower. This is by itself might be enough to choke performance as you are observing.
In addition, I do not like the practice of using "select *" syntax in a view. This potentially leaves "land mines." If a change is made to a base table and it is not remembered to fix the views the views are likely to yield unexpected results.
A populated table is going to perform two orders of magnitude faster because the tables can just read the data and crank out the query results without doing anywhere near the amount of processing that is required to process these views.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply