Cache SQL Server Views


    ## Statement [sql]
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
    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.
    2008-07-02T15:07:04+00:00
    347
    C#
    cache-sql-server-views
    9
    Cache SQL Server Views
    http://blog.myerscough.ac.uk
    2010-02-02T03:06:58+00:00
    663
    
      663
      http://gatekiller.myopenid.com
      GateKiller
      5.0
      6
      http://gatekiller.co.uk


    DDL
-------
CREATE TABLE `locations` (
  `id` bigint(20) NOT NULL auto_increment,
  `name` varchar(255) character set latin1 NOT NULL,
  `parent_id` bigint(20) default '0',
  `zip_code` varchar(255) character set latin1 default NULL,
  `lat` float default '0',
  `lng` float default '0',
  `order` int(11) default '0',
  PRIMARY KEY  (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=35 DEFAULT CHARSET=utf8;

CREATE TABLE `item_location_mappings` (
  `id` bigint(20) NOT NULL auto_increment,
  `location_id` bigint(20) NOT NULL,
  `item_id` bigint(20) NOT NULL,
  PRIMARY KEY  (`id`),
  KEY `IDX_LocationID` (`location_id`),
  KEY `IDX_ItemID` (`item_id`)
) ENGINE=MyISAM AUTO_INCREMENT=107 DEFAULT CHARSET=latin1;

CREATE TABLE `category_mappings` (
  `id` int(11) NOT NULL auto_increment,
  `item_id` int(11) NOT NULL,
  `category_id` int(11) NOT NULL,
  PRIMARY KEY  (`id`),
  KEY `IDX_CategoryID` (`category_id`),
  KEY `IDX_ItemID` (`item_id`)
) ENGINE=MyISAM AUTO_INCREMENT=198 DEFAULT CHARSET=latin1;


my written query 
----------------------

SELECT 
        l.*, 
        count(*) as weight
FROM locations l
	JOIN item_location_mappings ilm 
		ON ilm.location_id = l.id
	JOIN category_mappings cm
		ON cm.item_id = ilm.item_id
WHERE
	cm.category_id = 45
GROUP BY l.id
    i have to generate a list of locations with the following format - 
asia (100002)
bangladesh (50000)
dhaka (3000)

which is consist of the following format - 
<location name> (<total number of items from the same location>)

also all of these content are limited by specific category.
hope someone will come up with some refactoring suggestion :)
by the way, although i had indexing for location_id, item_id but the explain says the following report -
+----+-------------+-------+--------+---------------+---------+---------+---------------------------------+------+----------------------------------------------+
| id | select_type | table | type   | possible_keys | key     | key_len | ref                             | rows | Extra                                        |
+----+-------------+-------+--------+---------------+---------+---------+---------------------------------+------+----------------------------------------------+
|  1 | SIMPLE      | cm    | ALL    | NULL          | NULL    | NULL    | NULL                            |   19 | Using where; Using temporary; Using filesort | 
|  1 | SIMPLE      | ilm   | ALL    | NULL          | NULL    | NULL    | NULL                            |   38 | Using where                                  | 
|  1 | SIMPLE      | l     | eq_ref | PRIMARY       | PRIMARY | 8       | ads_development.ilm.location_id |    1 |                                              | 
+----+-------------+-------+--------+---------------+---------+---------+---------------------------------+------+----------------------------------------------+

    2007-12-05T10:37:34+00:00
    170
    Ruby
    optimize-query
    0
    optimize query
    
    2007-12-05T10:37:34+00:00
    379
    
      379
      http://we4tech.myopenid.com
      we4tech
      0.0
      6