CODEWITHSUNDEEP
sqlsql-server
user2171512
user2171512

Reputation: 561

Optimize sql code with temp table

how can i optimize this code in SQL

INSERT INTO #ActivePlayers ([PlayerId])
        SELECT DISTINCT([OwnerSID]) [PlayerId]            
        FROM [WarehouseMgmt].[FactLoginTrans] FLT
        JOIN [WarehouseMgmt].[DimPlayer] DP ON DP.[Id] = FLT.[OwnerSID]
        WHERE [IsSystemUser]=0
        AND [OwnerSID]>0
        AND [WarehouseReports].[ConvertfromUTCtoTZ] (FLT.[LogonTime],@ZoneCode) BETWEEN DATEADD(month, -13, @Date) AND DATEADD(month, -1, @Date)

What's the best option for [WarehouseReports].[ConvertfromUTCtoTZ] (FLT.[LogonTime],@ZoneCode) BETWEEN DATEADD(month, -13, @Date) AND DATEADD(month, -1, @Date) , because this take me some time and i repeat this line in few SQL queries. How to put this into temp table and use it later in query ?

Upvotes: 0

Views: 856

Answers (2)

Remus Rusanu
Remus Rusanu

Reputation: 294227

To optimize a SQL query you need to understand where the bottlenecks occur. Read How to analyse SQL Server performance.

The most important factor for SQL optimization is having a SARG-able predicate. In other words your WHERE clause and the JOINs must be able to use indexes. As is, your query is unsargable. An index on [WarehouseMgmt].[DimPlayer]([Id]) is required, but I'm pretty sure you already have one. Now look at your WHERE clause:

WHERE [IsSystemUser]=0
AND [OwnerSID]>0
AND [WarehouseReports].[ConvertfromUTCtoTZ] (FLT.[LogonTime],@ZoneCode) BETWEEN 
    DATEADD(month, -13, @Date) AND DATEADD(month, -1, @Date)

This cannot be made to use an index. The first two conditions can be discarded because of low carnality (too many rows will qualify). Which leaves the range predicate on the date range. By asking to convert the stored timezone into the parameter time zone you throw away any possible chance at optimizing the query because of the UDF. You should do the opposite:

  • make sure the [LogonTime] is store UTC (always store any date/time in the database as UTC)
  • convert the @date from user timezone to UTC:
    @utcdate = ConvertfromTZtoUTC] (@date,@ZoneCode)
  • express the range on [LogonTime]:
    LogonTime BETWEEN DATEADD(month, -13, @utcdate) AND DATEADD(month, -1, @utcdate)
  • have a covering index ([LogonTime]) INCLUDE ([IsSystemUser], [OwnerSID])

Start reading Designing Indexes.

In future when asking database queries, always include a complete schema of your tables, including all indexes.

Upvotes: 2

mickvav
mickvav

Reputation: 320

May be you have to do something like (warning, I write metacode, be sure to check your SQL server docs!)

 ALTER TABLE [WarehouseMgmt].[FactLoginTrans]
   ADD COLUMN BOOLEAN IsInPeriodOfInterest

UPDATE [WarehouseMgmt].[FactLoginTrans]
   SET IsInPeriodOfInterest = [WarehouseReports].[ConvertfromUTCtoTZ](FLT.[LogonTime], @ZoneCode)
       BETWEEN DATEADD(month, -13, @Date) AND DATEADD(month, -1, @Date)

And alter your further requests to refer to this field?

Or you can, of course, 

CREATE TEMPORARY TABLE (YOUR_PRIMARY_KEY, boolean IsInPeriodOfInterest)
SELECT YOUR_PRIMARY_KEY, [WarehouseReports].[ConvertfromUTCtoTZ](FLT.[LogonTime],@ZoneCode)
       BETWEEN DATEADD(month, -13, @Date) AND DATEADD(month, -1, @Date)
  FROM [WarehouseMgmt].[FactLoginTrans] FLT

and than join with it.

Upvotes: -1

Related Questions