98c852e2d9b26249745ea92c72964d3f

Question: Is there anyway to make this T-SQL statement faster?

Background:
One of our databases has a set of SQL View looking at the main database eg:

vCustomers
vPayments

This transact sql statement ocasionally makes a copy/cache of these views to improve read times in certain area's of the frontend application. (There is also an extra step which creates indexes but this is relatively fast).

The code will then covert the above views into the tables:

cCustomers
cPayments

Of course, the naming convention could be changed to suit your needs.

So is there any way to improve performance?

Many Thanks
Stephen

PS: There wasn't a SQL Language option.

Statement

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
Declare @Name varchar(128)
Declare @Cursor Cursor

Set @Cursor = Cursor For Select Name From SysObjects Where XType = 'V'

Open @Cursor

Fetch Next From @Cursor Into @Name
While @@Fetch_Status = 0
Begin
	Declare @Table varchar(128)
	Set @Table = 'c' + SubString(@Name, 2, 128)
	If Exists(Select Name From SysObjects Where Name = @Table)
	Begin
		Print 'Drop Table ' + @Table
		Exec('Drop Table ' + @Table)
	End
	Exec('Begin Tran Select * Into ' + @Table + ' From ' + @Name + ' Commit')
	Fetch Next From @Cursor Into @Name
End

Close @Cursor
Deallocate @Cursor

Refactorings

No refactoring yet !

C2953d47b6de83f3217b48c3584fab1c

Auron

July 2, 2008, July 02, 2008 16:55, permalink

No rating. Login to rate!

This code seems to work pretty straight forward. I can't see an easy way to increase performace.

You could add an extra column to cCustomers and cPayments with a boolean value. This column would indicate if the row is dirty or not. A dirty row can mean two things:

* The row has been modified. Changes are not cached yet.
* The row has been added. Row is not cached yet.

You'll need a trigger or two in order to automatically update the value of this row. Then, whenever you cache the views, don't drop the cache tables, just update or add the dirty rows to the cache.

Bye.

645d2a1d6ea85daa4dbdcfa0239fc11b

mike johnson

July 2, 2008, July 02, 2008 18:52, permalink

No rating. Login to rate!

One thing that might help is creating an Indexed View (Materialized View if you come from an Oracle background). this esentially creates a precomputed version of the view around that updates itself as records are added,deleted or modified. We have used these with success but there is some overhead when you update the underlying table. if you have a heavy transaction load against the underlying tables this might fix one problem but introduce another

you can read more here http://www.akadia.com/services/sqlsrv_matviews.html and in the BOOL.

69714938a17318a96a5d4673da8892ec

Mark Brackett

July 4, 2008, July 04, 2008 13:39, permalink

2 ratings. Login to rate!

Your cursor should be LOCAL READ_ONLY FAST_FORWARD.
Combine the DROP and SELECT statement into a single EXEC call.
Get rid of the BEGIN TRAN/COMMIT around the SELECT INTO - a single statement is always transactional.
Consider using a global temp table (##table), which could cut down on transaction logging (depending on db settings).
Consider caching in memory on another tier.
If you can just update changed rows on a timestamp column, that may help (depending on how many rows change, of course).
Optimize the view.

98c852e2d9b26249745ea92c72964d3f

GateKiller

July 7, 2008, July 07, 2008 11:33, permalink

No rating. Login to rate!

Thanks for the input guys :)

I could update the view based on the last time one of the rows was edited but I think that would decrease the performance of the view in general.

I also can't do indexed views because the views are in a different database to the source tables and this is not allowed in sql server 2005.

I've taken most of the feedback from Mark to make the below which seems a little bit faster :)

Cheers
Stephen

Statement

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
Declare @Name varchar(128)
Declare cCursor Cursor LOCAL FORWARD_ONLY FAST_FORWARD READ_ONLY For Select Name From SysObjects Where XType = 'V'

Open cCursor

Fetch Next From cCursor Into @Name
While @@Fetch_Status = 0
Begin
	Declare @Table varchar(128)
	Set @Table = 'c' + SubString(@Name, 2, 128)
	If Exists(Select Name From SysObjects Where Name = @Table)
	Begin
		Exec('Drop Table ' + @Table + ' Select * Into ##TempViews From ' + @Name + ' Select * Into ' + @Table + ' From ##TempViews Drop Table ##TempViews')
	End
	Else
	Begin
		Exec('Select * Into ##TempViews From ' + @Name + ' Select * Into ' + @Table + ' From ##TempViews Drop Table ##TempViews')
	End
	Fetch Next From cCursor Into @Name
End

Close cCursor
Deallocate cCursor
69f4fbbfa3d8fcef2e6d11f04db64c5b

Jonathan Starr

July 23, 2008, July 23, 2008 22:31, permalink

2 ratings. Login to rate!

Personally, I hate cursors.... Really. Hate. Cursors... Curse Them!

They consume lots of overhead, and are exceedingly slow.

Here's the equivalent way to write without the accursed cursors.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
Declare @Name varchar(128)

Select Name 
    Into #Views
    From SysObjects 
    Where XType = 'V'

While (select COUNT(*) from #Views) > 0
Begin
	Declare @Table varchar(128)
	Set @Table = 'c' + SubString(@Name, 2, 128)
	If Exists(Select Name From SysObjects Where Name = @Table)
	Begin
		Exec('Drop Table ' + @Table + ' Select * Into ##TempViews From ' + @Name + ' Select * Into ' + @Table + ' From ##TempViews Drop Table ##TempViews')
	End
	Else
	Begin
		Exec('Select * Into ##TempViews From ' + @Name + ' Select * Into ' + @Table + ' From ##TempViews Drop Table ##TempViews')
	End
	
	delete from #views where Name = @Name
	
	set @Name = (select top 1 Name from #Views)
End
98c852e2d9b26249745ea92c72964d3f

GateKiller

July 25, 2008, July 25, 2008 10:20, permalink

No rating. Login to rate!

Jonathan, Thanks very much for that elegant piece of code :) I'm in the process of replacing it with previous code I have.

This was the only addition I made at the end of the statement you provided.

I think from now on, I'll write my Cursor like functionality like you have :)

Cheers
Stephen

SQL Statement

1
Drop Table #Views
98c852e2d9b26249745ea92c72964d3f

GateKiller

July 25, 2008, July 25, 2008 10:50, permalink

1 rating. Login to rate!

Just a couple of more changes. Added square brakets around table name and moved the Set @Name to the start of the loop as it was failing on the first table.

SQL Statement

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
Declare @Name varchar(128)
Declare @Table varchar(128)

Select Name Into #Views From SysObjects Where XType = 'V'

While (Select Count(*) From #Views) > 0
Begin
	Set @Name = (Select top 1 Name From #Views)
	Set @Table = 'c' + SubString(@Name, 2, 128)
	If Exists(Select Name From SysObjects Where Name = @Table)
	Begin
		Exec('Drop Table [' + @Table + '] Select * Into [##TempViews] From [' + @Name + '] Select * Into [' + @Table + '] From [##TempViews] Drop Table [##TempViews]')
	End
	Else
	Begin
		Exec('Select * Into [##TempViews] From [' + @Name + '] Select * Into [' + @Table + '] From [##TempViews] Drop Table [##TempViews]')
	End
	
	Delete From #Views Where Name = @Name
End

Drop Table #Views

Your refactoring





Format Copy from initial code

or Cancel