View to Table - Synchronization
-
I have a complicated view that takes forever to search through, and so I created a table using SELECT * FROM <my view> INTO <my new table>. My problem is how to best update the table. It takes about 30 seconds to recreate the table, but during this time I can't access it. I'd be nice if I could just update the rows that'd changed, or maybe create a new table and then when thats done, replace it in one transaction with the new one so I wouldn't have a down period where the table would be inaccessible.
Any ideas?? Trigger?
John
-
Let's optimize the view first.
Can you send the code, the indexes definition and the execution plan?
-
Honestly sounds like you might benefit from an indexed view. Did you look into that instead of loading a table? Ony reason I ask is because the indexed view is pretty much what you're looking for and there are tools built in that will refresh it with minimal to no down time.
----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software
-
Yes.. but those views are very hard to create (because they have so many restrictions).
In case you do not wish to optimize or use an index view, then here's an answer I gave to a similar situation :
Process for this.
To create the table for the first time :
Select * into tblname from vwname where 1=0 --create only, doesn't load anything
Then add the necessary indexes.
Script the table using EM.
Change the script to create table tablename_temp
now to reload :
run the script with the renamed table.
Now insert the data into that table :
Insert into tbltemp (col1, col2) select col1, col2... from dbo.viewname
drop the current table where the managers fetch the data
rename the temp table
you're done.
Keep in mind that the constraints you create on that table must have a unique name, so they to must be renamed to _temp in the script, then renamed back to their orinigal name in the end (otherwise the script will fail).
-
Excellent stuff - like it.
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
-
OK I like that solution - so just create a trigger to do this at some interval?
Forgive my ignorance of SQL Server, but is there a way to just update the table with the new/updated rows? The table has about 33,000 rows and between updates there might be only 10-20 new or upated rows. I'd like to avoid pegging the cpu's on this machine for 33 seconds (time to repopulate this table) every 5 minutes, although that solution will work.
Thanks guys...
-
Yup, that's a great workaround, but it doesn't solve the underlying problem of the bad perforaming view.
The indexed view would do just that, but as I said it doesn't solve all problems because there's a $h!tload of restrictions that need to be respected to be able to create one.
BTW 1k rows/sec seems pretty slow to me. Are you sure that the view is as optimized as possible?
-
Here's the view. The problem is there is a web client that need this search to perform <1 sec (which it does when searching on the actual table), and currently searching on this view takes up to 30 seconds. The search runs on (potentially) every field using something like 'abc%'.
This view generates about 33-34,000 rows and I'm searching on a dual 3.0GHz Xeon w/ 2GB.
CREATE VIEW dbo.RFJobSearch
AS
SELECT a.procid, b.[value] AS ClientName, c.[value] AS CompanyName, d.[value] AS ClientRef, e.[value] AS JobNumber, f.[value] AS Title,
g.[value] AS FirstName, h.[value] AS Surname, i.[value] AS JobSummary, j.[value] AS SurveyPlannedStartDate, k.[value] AS SurveyPlannedStartTime,
l.[value] AS SurveyPlannedEndDate, m.[value] AS SurveyPlannedEndTime, n.[value] AS WorkPlannedStartDate, o.[value] AS WorkPlannedStartTime,
p.[value] AS WorkPlannedEndDate, q.[value] AS WorkPlannedEndTime, r.[value] AS RiskStreet1, s.[value] AS RiskStreet2, t.[value] AS RiskPostcode,
u.[value] AS RiskTown, v.[value] AS RiskCounty
FROM dbo.procs a INNER JOIN
dbo.rlvntdata b ON a.procid = b.procid INNER JOIN
dbo.rlvntdata c ON a.procid = c.procid INNER JOIN
dbo.rlvntdata d ON a.procid = d.procid INNER JOIN
dbo.rlvntdata e ON a.procid = e.procid INNER JOIN
dbo.rlvntdata f ON a.procid = f.procid INNER JOIN
dbo.rlvntdata g ON a.procid = g.procid INNER JOIN
dbo.rlvntdata h ON a.procid = h.procid INNER JOIN
dbo.rlvntdata i ON a.procid = i.procid INNER JOIN
dbo.rlvntdata j ON a.procid = j.procid INNER JOIN
dbo.rlvntdata k ON a.procid = k.procid INNER JOIN
dbo.rlvntdata l ON a.procid = l.procid INNER JOIN
dbo.rlvntdata m ON a.procid = m.procid INNER JOIN
dbo.rlvntdata n ON a.procid = n.procid INNER JOIN
dbo.rlvntdata o ON a.procid = o.procid INNER JOIN
dbo.rlvntdata p ON a.procid = p.procid INNER JOIN
dbo.rlvntdata q ON a.procid = q.procid INNER JOIN
dbo.rlvntdata r ON a.procid = r.procid INNER JOIN
dbo.rlvntdata s ON a.procid = s.procid INNER JOIN
dbo.rlvntdata t ON a.procid = t.procid INNER JOIN
dbo.rlvntdata u ON a.procid = u.procid INNER JOIN
dbo.rlvntdata v ON a.procid = v.procid
WHERE (a.parentprocid = 0) AND (b.rlvntdataname = 'ClientName') AND (c.rlvntdataname = 'CompanyName') AND
(d.rlvntdataname = 'CustomerReferenceNumber') AND (e.rlvntdataname = 'JobNumber') AND (f.rlvntdataname = 'Title') AND
(g.rlvntdataname = 'FirstName') AND (h.rlvntdataname = 'Surname') AND (i.rlvntdataname = 'JobSummary') AND (j.rlvntdataname = 'EstJobSDate') AND
(k.rlvntdataname = 'EstJobSTime') AND (l.rlvntdataname = 'EstJobEDate') AND (m.rlvntdataname = 'EstJobETime') AND
(n.rlvntdataname = 'WrkJobSDate') AND (o.rlvntdataname = 'WrkJobSTime') AND (p.rlvntdataname = 'WrkJobEDate') AND
(q.rlvntdataname = 'WrkJobETime') AND (r.rlvntdataname = 'Address') AND (s.rlvntdataname = 'Address2') AND (t.rlvntdataname = 'Postcode') AND
(u.rlvntdataname = 'Town') AND (v.rlvntdataname = 'County')
-
I've never actually found a situation that both required an indexed view yet was simple enough in it's design to be able to implement one, so I understand what you're saying. This one sounded, on first blush, simple enough. Remains to be seen in this case.
----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software
-
This query cannot be used in an indexed view, so that solution is out.
Now why do you have 20 self joins on this query??
Can we see the table definition?
Are they allowed to do searches like : col like '%ABD%?
What indexes are in place?
-
There's a wierd table setup where the rows are in a way acting as columns also... a 3 dimensional table I guess (I didn't create it and don't have the def). How does that 1k/sec sound now?
Yes, they can do wildcard searches.
-
I think you're gonna have to use the trick with a perm table that holds that view... then hope you can optmize it enough to go subsecond with it (harder with wildcard).
-
I have the perm table, the problem is how to update it without deleting it. right now i have:
CREATE TRIGGER updateTable ON [dbo].[rlvntdata]
FOR INSERT, UPDATE
AS
select * into temp from RFJobSearchView;
drop table RFJobSearchTable;
<rename the temp table to RFJobSearchTable>
The reason I'm using a trigger is the rlvnt table (which is a source for the view) is replicated every 5 minutes, so this would run every 5 minutes.
Two questions:
1. whats the syntax to rename a table. Doesn't look like SQL Server supports RENAME
2. will this work?? I hope this will get called every time the table is replicated... Is there a way to just run this every 15 minutes or so?
-
If there's nothing else updating that table I guess it's ok. But I wouldn't that operation there. I would raise an alert that would start a job that would do that (so the trigger doesn't hang or makes something else fail).
That being :
Script the table using EM.
Change the script to create a temp table tablename_temp
now to reload :
run the script with the renamed table.
Now insert the data into that table :
Insert into tbltemp (col1, col2) select col1, col2... from dbo.viewname
drop the current table where the users fetch the data
rename the temp table (exec sp_rename 'OldName', 'NewName', 'OBJECT')
you're done.
Keep in mind that the constraints you create on that temp table must have a unique name (to the whole db), so they to must be renamed to _temp too in the script, then renamed back to their orinigal name in the end (otherwise the script will fail when you try to recreate the temp table the next time around).
-
The idea is this happens automatically. How do I run this script on a schedule?
Viewing 15 posts - 1 through 15 (of 16 total)
You must be logged in to reply to this topic. Login to reply