[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Index a temp table

Posted on 2011-05-13
32
Medium Priority
?
407 Views
Last Modified: 2012-06-27
Hi,

I need to, I think, create an index on the temp table that I have attached as code to this question. The script was created to being back reults where the date is a parameter, the thing I need to index is the serviceType then countryCode, cityCode, storeType and storeCode.

I have a crystal report that runs this function and the reports are defined by the service type then split between country, city, storetypes and stores. The function only had a few results prior to a few days ago, now it has around half a million records to process so i'm thinking that if I index the temp table created here it would speed it up a lot, it takes around an hour and 10 minutes to complete. This would be too bad but the user of the report that uses this data has the flexibility to select different dates to get the results, up to 7 days ahead so. If they didn't and it only returned results for the next day I'd just run the function during the night and we may not have an issue.

So I think putting an index on the columns above may help here?

Any help would be much appreciated.

Regards,

Ken

0
Comment
Question by:kenuk110
  • 17
  • 10
  • 4
  • +1
32 Comments
 

Author Comment

by:kenuk110
ID: 35753435
Sorry, I forgot the code!
ALTER FUNCTION [dbo].[ServiceViewBatch] (@date datetime)
RETURNS @cte table
(
    -- columns returned by the function
	[ServiceValue] decimal NULL,
	[ItemName] nvarchar (255) NULL,
	[countryCode] [nvarchar](3) NOT NULL,
	[storeCode] [nvarchar](3) NOT NULL,
	[cityCode] [nvarchar](2) NOT NULL,
	--[itemCode] [nvarchar](1000) NOT NULL,
	[itemCodeParsed] [nvarchar] (255) NULL,
	[storeType] [nvarchar](1) NOT NULL,
	[vendorCode] [nvarchar](3) NOT NULL,
	[upcCode] [nvarchar](30) NOT NULL,
	[rc] [nvarchar](3)  NULL,
	[gridCodeX] [nvarchar](4) NOT NULL,
	[gridCodeY] [nvarchar](4) NOT NULL,
	[facings] [nvarchar](3) NULL,
	[brand] [nvarchar](255) NULL,
	[serviceDay] [nvarchar](9) NULL,
	[serviceType] [nvarchar](5) NULL,
	[ARABIC] [nvarchar](255) NULL,
	[TREF] [nvarchar](255) NULL,
	[serviceValueTotal] [decimal](29, 2) NULL,
	[rnum] [bigint] NULL,
	[RunTotal] [decimal](38, 2) NULL,
	[BatchGroup] [int] NULL)


AS
-- body of the function
BEGIN

declare @ctetemp table
(
    -- columns returned by the function
	[ServiceValue] decimal NULL,
	[ItemName] nvarchar (255) NULL,
	[countryCode] [nvarchar](3) NOT NULL,
	[storeCode] [nvarchar](3) NOT NULL,
	[cityCode] [nvarchar](2) NOT NULL,
	--[itemCode] [nvarchar](1000) NOT NULL,
	[itemCodeParsed] [nvarchar] (255) NULL,
	[storeType] [nvarchar](1) NOT NULL,
	[vendorCode] [nvarchar](3) NOT NULL,
	[upcCode] [nvarchar](30) NOT NULL,
	[rc] [nvarchar](3)  NULL,
	[gridCodeX] [nvarchar](4) NOT NULL,
	[gridCodeY] [nvarchar](4) NOT NULL,
	[facings] [nvarchar](3) NULL,
	[brand] [nvarchar](255) NULL,
	[serviceDay] [nvarchar](9) NULL,
	[serviceType] [nvarchar](5) NULL,
	[ARABIC] [nvarchar](255) NULL,
	[TREF] [nvarchar](255) NULL,
	[serviceValueTotal] [decimal](29, 2) NULL,
	[rnum] [bigint] NULL);
	
insert into @ctetemp
  SELECT [ServiceValue]
       , [ItemName]
       , [countryCode]
       , [storeCode]
       , [cityCode]
       --, [itemCode]
       , [itemCodeParsed]
       , [storeType]
       , [vendorCode]
       , [upcCode]
       , [rc]
       , [gridCodeX]
       , [gridCodeY]
       , [facings]
       , [brand]
       , [serviceDay]
       , [serviceType]
       , [ARABIC]
       , [TREF]
       , [serviceValueTotal]
       , ROW_NUMBER() 
            OVER (PARTITION BY countryCode, cityCode, storeType, storeCode ORDER BY CountryCode, CityCode, StoreCode, storeType, RC, gridCodeX) rnum
  FROM [T1].[dbo].[ServiceSchedule2]  s
  WHERE serviceday = DATENAME(WEEKDAY, @date)
    AND ( ServiceType <> 'IDR' 
        OR NOT EXISTS( select null from [T1].[dbo].[ServiceSchedule2] o
                   where o.UPCcode = s.UPCcode         
                     and o.ServiceDay = s.ServiceDay
                     AND o.ServiceType = 'SRR'
                     )
        )


INSERT INTO @cte
SELECT a.*
     , rt.svSum as RunTotal
     , convert(int, ceiling(rt.svSum/750)) as BatchGroup
FROM @ctetemp a 
CROSS APPLY(
  SELECT SUM(serviceValueTotal) svSum
  FROM @ctetemp b
  WHERE b.rnum <= a.rnum
AND b.countryCode = a.countryCode AND  b.cityCode = a.cityCode AND b.storeType = a.storeType AND b.storeCode = a.storeCode
) rt 

RETURN
END

Open in new window

0
 
LVL 18

Expert Comment

by:deighton
ID: 35753464
I don't think you can add it to a table variable.


Personally I suspect here that Crystal is being handed too many rows, you would be best looking at using the power of SQL server to do the processing you need, then handing Crystal just what it needs to show.  If you can.  

Is Crystal displaying all those millions of records, or a summary of them?
0
 

Author Comment

by:kenuk110
ID: 35753510
Hi,

SQL already narrows the results down just to the day I need then crystal only displays the results based on the 'serviceType'. So to answer your question SQL does narrow them down then crystal brings back the results based on serviceType.

I need the results in one table also due to some other functionality I need, the results have a total column that seperates the results in to number totalling 750 so I need them all in one table, I then let Crystal just take the ones it needs.

I thought that putting an index on the temp table would help with speed here?
0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
LVL 57

Expert Comment

by:Raja Jegan R
ID: 35753521
Instead of Table variable, Create a Temp table to create index..

And create the below index to see some performance gains.

Create index ix_index_name on [dbo].[ServiceSchedule2](ServiceDay, ServiceType, UPCcode)
create table #ctetemp 
(
    -- columns returned by the function
	[ServiceValue] decimal NULL,
	[ItemName] nvarchar (255) NULL,
	[countryCode] [nvarchar](3) NOT NULL,
	[storeCode] [nvarchar](3) NOT NULL,
	[cityCode] [nvarchar](2) NOT NULL,
	--[itemCode] [nvarchar](1000) NOT NULL,
	[itemCodeParsed] [nvarchar] (255) NULL,
	[storeType] [nvarchar](1) NOT NULL,
	[vendorCode] [nvarchar](3) NOT NULL,
	[upcCode] [nvarchar](30) NOT NULL,
	[rc] [nvarchar](3)  NULL,
	[gridCodeX] [nvarchar](4) NOT NULL,
	[gridCodeY] [nvarchar](4) NOT NULL,
	[facings] [nvarchar](3) NULL,
	[brand] [nvarchar](255) NULL,
	[serviceDay] [nvarchar](9) NULL,
	[serviceType] [nvarchar](5) NULL,
	[ARABIC] [nvarchar](255) NULL,
	[TREF] [nvarchar](255) NULL,
	[serviceValueTotal] [decimal](29, 2) NULL,
	[rnum] [bigint] NULL);
	
insert into #ctetemp

Open in new window

0
 
LVL 4

Expert Comment

by:qasim_md
ID: 35753530
You cannot add an index to the table variable, however you can add a primary key constraint to the table variable, that is if invoiceid is unique. You get an index with a PK constraint.

DECLARE @tempTable1 TABLE (invoiceid int PRIMARY KEY CLUSTERED)

If the Data is more better to go with the #temp tables instead of the @table_variables
0
 

Author Comment

by:kenuk110
ID: 35753540
Just for info, the temp table that is created gets its data from a view initially, I have attached the code to this message.
WITH PreResult AS (SELECT     dbo.[cp.service.option].ServiceValue, dbo.products.ItemName, dbo.item.countryCode, dbo.item.storeCode, dbo.item.cityCode, dbo.item.itemCode, 
                                                                   dbo.item.itemCodeParsed, dbo.item.storeType, dbo.item.vendorCode, dbo.item.upcCode, dbo.products.RC, dbo.item.gridCodeX, 
                                                                   dbo.item.gridCodeY, dbo.item.facings, dbo.item.brand, dbo.scheduleN.serviceDay, dbo.scheduleN.serviceType, dbo.products.[UPC Code], 
                                                                   dbo.products.TREF, (CASE WHEN dbo.item.facings BETWEEN 1 AND 4 THEN 1 WHEN dbo.item.facings BETWEEN 5 AND 
                                                                   9 THEN 2 WHEN dbo.item.facings BETWEEN 10 AND 14 THEN 3 WHEN dbo.item.facings BETWEEN 15 AND 
                                                                   20 THEN 4 WHEN dbo.item.facings BETWEEN 21 AND 24 THEN 5 WHEN dbo.item.facings BETWEEN 25 AND 
                                                                   29 THEN 6 WHEN dbo.item.facings BETWEEN 30 AND 34 THEN 7 WHEN dbo.item.facings BETWEEN 35 AND 
                                                                   39 THEN 8 WHEN dbo.item.facings BETWEEN 40 AND 44 THEN 9 WHEN dbo.item.facings BETWEEN 45 AND 49 THEN 10 ELSE 1 END) 
                                                                   AS facingsCount, dbo.store.classCode, dbo.products.ARABIC
                                            FROM         dbo.products LEFT OUTER JOIN
                                                                   dbo.[cp.service.option] ON dbo.products.RC = dbo.[cp.service.option].rc RIGHT OUTER JOIN
                                                                   dbo.store INNER JOIN
                                                                   dbo.item INNER JOIN
                                                                   dbo.scheduleN ON dbo.item.upcCode = dbo.scheduleN.upcCode ON dbo.store.cityCode = dbo.item.cityCode AND 
                                                                   dbo.store.countryCode = dbo.item.countryCode AND dbo.store.storeCode = dbo.item.storeCode AND 
                                                                   dbo.store.vendorCode = dbo.item.vendorCode AND dbo.store.storeType = dbo.item.storeType AND 
                                                                   dbo.store.countryCode = dbo.scheduleN.countryCode AND dbo.store.vendorCode = dbo.scheduleN.vendorCode AND 
                                                                   dbo.store.classCode = dbo.scheduleN.classCode AND dbo.store.storeType = dbo.scheduleN.storeType ON 
                                                                   dbo.[cp.service.option].ServiceType = dbo.scheduleN.serviceType AND dbo.products.[UPC Code] = dbo.scheduleN.upcCode)
    SELECT     TOP (100) PERCENT ServiceValue, ItemName, countryCode, storeCode, classCode, cityCode, itemCode, itemCodeParsed, storeType, vendorCode, upcCode, 
                            gridCodeX, gridCodeY, facings, brand, serviceDay, serviceType, [UPC Code], ARABIC, TREF, facingsCount, facingsCount * ServiceValue AS ServiceValueTotal, 
                            RC
     FROM         PreResult AS PreResult_1
     ORDER BY serviceDay, serviceType, countryCode, cityCode, storeCode, vendorCode, gridCodeX

Open in new window

WITH PreResult AS (SELECT     dbo.[cp.service.option].ServiceValue, dbo.products.ItemName, dbo.item.countryCode, dbo.item.storeCode, dbo.item.cityCode, dbo.item.itemCode, 
                                                                   dbo.item.itemCodeParsed, dbo.item.storeType, dbo.item.vendorCode, dbo.item.upcCode, dbo.products.RC, dbo.item.gridCodeX, 
                                                                   dbo.item.gridCodeY, dbo.item.facings, dbo.item.brand, dbo.scheduleN.serviceDay, dbo.scheduleN.serviceType, dbo.products.[UPC Code], 
                                                                   dbo.products.TREF, (CASE WHEN dbo.item.facings BETWEEN 1 AND 4 THEN 1 WHEN dbo.item.facings BETWEEN 5 AND 
                                                                   9 THEN 2 WHEN dbo.item.facings BETWEEN 10 AND 14 THEN 3 WHEN dbo.item.facings BETWEEN 15 AND 
                                                                   20 THEN 4 WHEN dbo.item.facings BETWEEN 21 AND 24 THEN 5 WHEN dbo.item.facings BETWEEN 25 AND 
                                                                   29 THEN 6 WHEN dbo.item.facings BETWEEN 30 AND 34 THEN 7 WHEN dbo.item.facings BETWEEN 35 AND 
                                                                   39 THEN 8 WHEN dbo.item.facings BETWEEN 40 AND 44 THEN 9 WHEN dbo.item.facings BETWEEN 45 AND 49 THEN 10 ELSE 1 END) 
                                                                   AS facingsCount, dbo.store.classCode, dbo.products.ARABIC
                                            FROM         dbo.products LEFT OUTER JOIN
                                                                   dbo.[cp.service.option] ON dbo.products.RC = dbo.[cp.service.option].rc RIGHT OUTER JOIN
                                                                   dbo.store INNER JOIN
                                                                   dbo.item INNER JOIN
                                                                   dbo.scheduleN ON dbo.item.upcCode = dbo.scheduleN.upcCode ON dbo.store.cityCode = dbo.item.cityCode AND 
                                                                   dbo.store.countryCode = dbo.item.countryCode AND dbo.store.storeCode = dbo.item.storeCode AND 
                                                                   dbo.store.vendorCode = dbo.item.vendorCode AND dbo.store.storeType = dbo.item.storeType AND 
                                                                   dbo.store.countryCode = dbo.scheduleN.countryCode AND dbo.store.vendorCode = dbo.scheduleN.vendorCode AND 
                                                                   dbo.store.classCode = dbo.scheduleN.classCode AND dbo.store.storeType = dbo.scheduleN.storeType ON 
                                                                   dbo.[cp.service.option].ServiceType = dbo.scheduleN.serviceType AND dbo.products.[UPC Code] = dbo.scheduleN.upcCode)
    SELECT     TOP (100) PERCENT ServiceValue, ItemName, countryCode, storeCode, classCode, cityCode, itemCode, itemCodeParsed, storeType, vendorCode, upcCode, 
                            gridCodeX, gridCodeY, facings, brand, serviceDay, serviceType, [UPC Code], ARABIC, TREF, facingsCount, facingsCount * ServiceValue AS ServiceValueTotal, 
                            RC
     FROM         PreResult AS PreResult_1
     ORDER BY serviceDay, serviceType, countryCode, cityCode, storeCode, vendorCode, gridCodeX

Open in new window

0
 
LVL 18

Expert Comment

by:deighton
ID: 35753552
you can only add a unique index to a table variable, via a primary key I believe

http://msdn.microsoft.com/en-us/library/aa175774%28v=sql.80%29.aspx
0
 
LVL 18

Expert Comment

by:deighton
ID: 35753570
what's taking the time?   Is it the creation of the table variable or the running of the report?  If it is creating the table variable that is taking too long, you need to performance tune the SQL populating the table variable
0
 
LVL 57

Expert Comment

by:Raja Jegan R
ID: 35753573
Have you tried creating the index specified in my comment http:#a35753521
0
 

Author Comment

by:kenuk110
ID: 35753592
Hi rrjegan17,

I thin kyou created this in the first place and it works great, it's just now I have added more data to the system it's slowed down a lot and I remember you telling me you were unsure of how it would perform.

I'm no expert on SQL, where would I put the amended script you posted, I have posted again the script that you created the first time, sorry to be stupid.

Cheers,

Ken
ALTER FUNCTION [dbo].[ServiceViewBatch] (@date datetime)
RETURNS @cte table
(
    -- columns returned by the function
	[ServiceValue] decimal NULL,
	[ItemName] nvarchar (255) NULL,
	[countryCode] [nvarchar](3) NOT NULL,
	[storeCode] [nvarchar](3) NOT NULL,
	[cityCode] [nvarchar](2) NOT NULL,
	--[itemCode] [nvarchar](1000) NOT NULL,
	[itemCodeParsed] [nvarchar] (255) NULL,
	[storeType] [nvarchar](1) NOT NULL,
	[vendorCode] [nvarchar](3) NOT NULL,
	[upcCode] [nvarchar](30) NOT NULL,
	[rc] [nvarchar](3)  NULL,
	[gridCodeX] [nvarchar](4) NOT NULL,
	[gridCodeY] [nvarchar](4) NOT NULL,
	[facings] [nvarchar](3) NULL,
	[brand] [nvarchar](255) NULL,
	[serviceDay] [nvarchar](9) NULL,
	[serviceType] [nvarchar](5) NULL,
	[ARABIC] [nvarchar](255) NULL,
	[TREF] [nvarchar](255) NULL,
	[serviceValueTotal] [decimal](29, 2) NULL,
	[rnum] [bigint] NULL,
	[RunTotal] [decimal](38, 2) NULL,
	[BatchGroup] [int] NULL)


AS
-- body of the function
BEGIN

declare @ctetemp table
(
    -- columns returned by the function
	[ServiceValue] decimal NULL,
	[ItemName] nvarchar (255) NULL,
	[countryCode] [nvarchar](3) NOT NULL,
	[storeCode] [nvarchar](3) NOT NULL,
	[cityCode] [nvarchar](2) NOT NULL,
	--[itemCode] [nvarchar](1000) NOT NULL,
	[itemCodeParsed] [nvarchar] (255) NULL,
	[storeType] [nvarchar](1) NOT NULL,
	[vendorCode] [nvarchar](3) NOT NULL,
	[upcCode] [nvarchar](30) NOT NULL,
	[rc] [nvarchar](3)  NULL,
	[gridCodeX] [nvarchar](4) NOT NULL,
	[gridCodeY] [nvarchar](4) NOT NULL,
	[facings] [nvarchar](3) NULL,
	[brand] [nvarchar](255) NULL,
	[serviceDay] [nvarchar](9) NULL,
	[serviceType] [nvarchar](5) NULL,
	[ARABIC] [nvarchar](255) NULL,
	[TREF] [nvarchar](255) NULL,
	[serviceValueTotal] [decimal](29, 2) NULL,
	[rnum] [bigint] NULL);
	
insert into @ctetemp
  SELECT [ServiceValue]
       , [ItemName]
       , [countryCode]
       , [storeCode]
       , [cityCode]
       --, [itemCode]
       , [itemCodeParsed]
       , [storeType]
       , [vendorCode]
       , [upcCode]
       , [rc]
       , [gridCodeX]
       , [gridCodeY]
       , [facings]
       , [brand]
       , [serviceDay]
       , [serviceType]
       , [ARABIC]
       , [TREF]
       , [serviceValueTotal]
       , ROW_NUMBER() 
            OVER (PARTITION BY countryCode, cityCode, storeType, storeCode ORDER BY CountryCode, CityCode, StoreCode, storeType, RC, gridCodeX) rnum
  FROM [T1].[dbo].[ServiceSchedule2]  s
  WHERE serviceday = DATENAME(WEEKDAY, @date)
    AND ( ServiceType <> 'IDR' 
        OR NOT EXISTS( select null from [T1].[dbo].[ServiceSchedule2] o
                   where o.UPCcode = s.UPCcode         
                     and o.ServiceDay = s.ServiceDay
                     AND o.ServiceType = 'SRR'
                     )
        )


INSERT INTO @cte
SELECT a.*
     , rt.svSum as RunTotal
     , convert(int, ceiling(rt.svSum/750)) as BatchGroup
FROM @ctetemp a 
CROSS APPLY(
  SELECT SUM(serviceValueTotal) svSum
  FROM @ctetemp b
  WHERE b.rnum <= a.rnum
AND b.countryCode = a.countryCode AND  b.cityCode = a.cityCode AND b.storeType = a.storeType AND b.storeCode = a.storeCode
) rt 

RETURN
END

Open in new window

0
 

Author Comment

by:kenuk110
ID: 35753606
Hi rrjegan17

I ran the script on the serviceschedule2 view but I get this error:

1> Create index ix_index_name on [dbo].[ServiceSchedule2](ServiceDay, ServiceType, UPCcode)
2> go
42000 - [SQL Server]Cannot create index on view 'ServiceSchedule2' because the view is not schema bound.
1>
0
 
LVL 57

Expert Comment

by:Raja Jegan R
ID: 35753615
Sorry for the confusion, kenuk110

You can't create Temp tables inside Functions ( I thought you are using Procedure and hence asked you to change it to Temp table)

The only way we can tune your query  is by creating this index alone:

Create index ix_index_name on [dbo].[ServiceSchedule2](ServiceDay, ServiceType, UPCcode)
0
 
LVL 57

Expert Comment

by:Raja Jegan R
ID: 35753621
>> Cannot create index on view 'ServiceSchedule2' because the view is not schema bound.

So it happens to be a view and not a table.
In that case, can you post the script of that view to suggest creation of appropriate indexes.
0
 

Author Comment

by:kenuk110
ID: 35753628
Hi again, yes, sorry, it gets the data from a view, I have attached the view SQL.

Thanks
 
WITH PreResult AS (SELECT     dbo.[cp.service.option].ServiceValue, dbo.products.ItemName, dbo.item.countryCode, dbo.item.storeCode, dbo.item.cityCode, dbo.item.itemCode, 
                                                                   dbo.item.itemCodeParsed, dbo.item.storeType, dbo.item.vendorCode, dbo.item.upcCode, dbo.products.RC, dbo.item.gridCodeX, 
                                                                   dbo.item.gridCodeY, dbo.item.facings, dbo.item.brand, dbo.scheduleN.serviceDay, dbo.scheduleN.serviceType, dbo.products.[UPC Code], 
                                                                   dbo.products.TREF, (CASE WHEN dbo.item.facings BETWEEN 1 AND 4 THEN 1 WHEN dbo.item.facings BETWEEN 5 AND 
                                                                   9 THEN 2 WHEN dbo.item.facings BETWEEN 10 AND 14 THEN 3 WHEN dbo.item.facings BETWEEN 15 AND 
                                                                   20 THEN 4 WHEN dbo.item.facings BETWEEN 21 AND 24 THEN 5 WHEN dbo.item.facings BETWEEN 25 AND 
                                                                   29 THEN 6 WHEN dbo.item.facings BETWEEN 30 AND 34 THEN 7 WHEN dbo.item.facings BETWEEN 35 AND 
                                                                   39 THEN 8 WHEN dbo.item.facings BETWEEN 40 AND 44 THEN 9 WHEN dbo.item.facings BETWEEN 45 AND 49 THEN 10 ELSE 1 END) 
                                                                   AS facingsCount, dbo.store.classCode, dbo.products.ARABIC
                                            FROM         dbo.products LEFT OUTER JOIN
                                                                   dbo.[cp.service.option] ON dbo.products.RC = dbo.[cp.service.option].rc RIGHT OUTER JOIN
                                                                   dbo.store INNER JOIN
                                                                   dbo.item INNER JOIN
                                                                   dbo.scheduleN ON dbo.item.upcCode = dbo.scheduleN.upcCode ON dbo.store.cityCode = dbo.item.cityCode AND 
                                                                   dbo.store.countryCode = dbo.item.countryCode AND dbo.store.storeCode = dbo.item.storeCode AND 
                                                                   dbo.store.vendorCode = dbo.item.vendorCode AND dbo.store.storeType = dbo.item.storeType AND 
                                                                   dbo.store.countryCode = dbo.scheduleN.countryCode AND dbo.store.vendorCode = dbo.scheduleN.vendorCode AND 
                                                                   dbo.store.classCode = dbo.scheduleN.classCode AND dbo.store.storeType = dbo.scheduleN.storeType ON 
                                                                   dbo.[cp.service.option].ServiceType = dbo.scheduleN.serviceType AND dbo.products.[UPC Code] = dbo.scheduleN.upcCode)
    SELECT     TOP (100) PERCENT ServiceValue, ItemName, countryCode, storeCode, classCode, cityCode, itemCode, itemCodeParsed, storeType, vendorCode, upcCode, 
                            gridCodeX, gridCodeY, facings, brand, serviceDay, serviceType, [UPC Code], ARABIC, TREF, facingsCount, facingsCount * ServiceValue AS ServiceValueTotal, 
                            RC
     FROM         PreResult AS PreResult_1
     ORDER BY serviceDay, serviceType, countryCode, cityCode, storeCode, vendorCode, gridCodeX

Open in new window

0
 
LVL 57

Expert Comment

by:Raja Jegan R
ID: 35753889
Then try creating this index which can really help:

create index ix_index_name on dbo.scheduleN(serviceDay, serviceType);
0
 

Author Comment

by:kenuk110
ID: 35754149
Okay, I've updated this and am running the function again, I'll post back when I get the results.

Is this all I have to do, run the create on that table?

Regards,

Ken
0
 

Author Comment

by:kenuk110
ID: 35754201
It's been running for around 30 minutes now, I'll see when it finishes anyway but this process needs to retrieve results in around 10-20 seconds really, I'm sure I'll have to look at reconfiguring what is happening here but I'm not sure what to do right now, this needs to be up and running by tomorrow. Is there any other way of making this faster or retrieving the data in another way so it's faster right now??

Regards,

Ken
0
 

Author Comment

by:kenuk110
ID: 35754261
The end result here is for the Crystal Reports I have to display the returned records. I have a web site with the report attached to it, I use the .net Crystal tools. Inside the report I have the option for the user to select a city they wish to print, obviously the report needs access to all the records in the temp table when it tries to retrieve the records. One way I thought would be to create an amount of temp tables relating to the cityCode then have the same amount of Crystal reports pointing to those tables. I know this isn't the best thing to do but if this speeds it up for tomorrow I can create a load of Crystal Reports pointing to new tables. It's the temp table part I'm not familiar with, I don't know how to seperate the results by countryCode, cityCode and if possible how to also seperate by storeType, storeCode.

Would this help things or am I thinking too much?

Regards,

Ken
0
 

Author Comment

by:kenuk110
ID: 35754403
I cancelled the original try I had with the index on scheduleN. It was still running around an hour long.
0
 
LVL 57

Expert Comment

by:Raja Jegan R
ID: 35755390
>> It's been running for around 30 minutes now,

I got stuck in a work and sorry for the delay..
Hope it is finished by now.
And in the meanwhile, do you have huge no. of records in your table scheduleN since the Index creation is taking large amount of time.
Once the index is created, your query / function should perform better.

>> It's the temp table part I'm not familiar with, I don't know how to seperate the results by countryCode, cityCode and if possible how to also seperate by storeType, storeCode.

In the final SELECT statement, you can fetch countryCode, cityCode columns alone or as required with storeType and storeCode..
0
 

Author Comment

by:kenuk110
ID: 35755701
I have 32107 records in ScheduleN, I'm not sure if this is a lot or not to be honest? I'm running the function again no, how do I tell if the index has been created?

As for the splitting part, would this make sense if I did it? Also, I should call the temp tables @ctetemp_london, @ctetemp_liverpool etc?
0
 

Author Comment

by:kenuk110
ID: 35755880
Is there anything i can do to improve the view do you know? Would it help even?
0
 

Author Comment

by:kenuk110
ID: 35756603
Hi again,

I thought I'd reduce the scope of the view to only have serviceDay = saturday and city = 02 but even with just that it takes 39 minutes. I also tried changing the @ctetemp to #ctetemp but it gave me an error. I can try to reproduce it if it'll help?

Regards,

Ken
0
 
LVL 57

Expert Comment

by:Raja Jegan R
ID: 35758707
>> I have 32107 records in ScheduleN, I'm not sure if this is a lot or not to be honest?

Its not pretty much and kindly confirm whether you have Primary key created on that table or not.

>> I'm running the function again no, how do I tell if the index has been created?

If you are using SSMS, expand this table and then expand under Indexes to see whether this index is created or not.

>> I thought I'd reduce the scope of the view to only have serviceDay = saturday and city = 02 but even with just that it takes 39 minutes.

So, your view is the one which is taking more no. of time and hence your function is not working faster.
We need to try tuning your View and kindly confirm whether you have both Primary key and the index which I suggested are created on that table.
If not, create both and try once again.
0
 

Author Comment

by:kenuk110
ID: 35760374
Hi,

The table does have a primary key, it's called pk and the index is as you requested. There is another table that the view picks data from, it's called 'item', this also has a primary key called 'pk' and an index on countryCode, cityCode, storeType, storeCode and gridCodeX.
0
 
LVL 57

Expert Comment

by:Raja Jegan R
ID: 35766617
Ok, then you don't have index on these two columns, right..
Try once after creating this index.

create index ix_index_name on dbo.scheduleN(serviceDay, serviceType);
0
 

Author Comment

by:kenuk110
ID: 36001401
Hi guys,

Really sorry for not responding to this earlier, I went on holiday and have only just got back in to the swing of things.

I'm trying the suggestion now, I'll update when it's done.

Regards,

Ken
0
 
LVL 18

Expert Comment

by:deighton
ID: 36009643
putting an index on a temp table can be problematic if there are two users running the report at once, you can end up with two similarly named index objects and throw an error when you try to add the second index.
0
 

Author Comment

by:kenuk110
ID: 36009724
Hi,

I'm not sure what to do ahere, the query bases it's retrieval of information of a day, the query is attached to a Crystal Report and hve a parameter in it that passes the date value to MSSQL. Originally I tried having this info in a table but it worked too slowly so we put it in a temp table and it whizzed along.

Just not sure what I can do and don't have the technical capacity in MSSQL or SQL to thin kof another way of doing this.
0
 
LVL 57

Expert Comment

by:Raja Jegan R
ID: 36009974
deighton,

>> putting an index on a temp table can be problematic if there are two users running the report at once, you can end up with two similarly named index objects and throw an error when you try to add the second index

I don't think so..
just try running the below code from two sessions of SSMS and both will work..

create table #temp ( a int, b int, c int)

create index ix on #temp(a, b)
create index iy on #temp(a, c)

So, it will not throw an error since it is based on temp table.

kenuk110,

You can go ahead with creating index on temp table which will work just fine..
0
 

Author Comment

by:kenuk110
ID: 36124440
Hi,

I tried that also but didn't seem to do anything for the performance. Can I just tell you that when I run the reports which get data from the temp table, the function is run again and again, anytime they refresh the report. The data is renewed each time so I'm wondering if these index's on this table will be doing anything as the data is refreshed each time. Am I correct in my thinking?
0
 
LVL 57

Accepted Solution

by:
Raja Jegan R earned 2000 total points
ID: 36127282
>> Can I just tell you that when I run the reports which get data from the temp table, the function is run again and again, anytime they refresh the report.

Do you mean the function is getting executed once for each and every records present in your temp table.
Since your function is table valued, it will be executed for all records in the temp table for the @date input.

In that case, then you need to change the logic of using that particular function and apply the logic directly to all records in the temp table once which would help improve the performance.
If your Table valued function is executed several times, then adding an index in function will not really help.
0

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
Windocks is an independent port of Docker's open source to Windows.   This article introduces the use of SQL Server in containers, with integrated support of SQL Server database cloning.
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.
Suggested Courses

830 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question