Re: Performance Issues With Query

From: David Portas (REMOVE_BEFORE_REPLYING_dportas_at_acm.org)
Date: 12/14/04

  • Next message: David Portas: "Re: Performance Issues With Query" 
    Date: Tue, 14 Dec 2004 20:48:13 -0000

    <brutal criticism>

    http://www.aspfaq.com/etiquette.asp?id=5006 

    -- 
David Portas
SQL Server MVP
--
<shop@pacifictabla.com> wrote in message 
news:1103046647.353450.322320@c13g2000cwb.googlegroups.com...
> Hi:
>
> I have a DTS package that does the following:
>
> 1. Data Pump: Grabs a table from a Sybase database, all fields and all
> records, and puts it in SQL Table 1.
> 2. Data Pump: Selects only certain records and puts them in a
> particular order, writes this to SQL Table 2.
> 3. SQL Task: SQL Code to parse the data and write it to SQL Table 3.
> One CallID previously had many events, now it is condensed to one
> record per Call.
>
> Task one and two run in an acceptable amount of time, but step 3 takes
> about an hour (just processing on my laptop right now, it will be
> faster on the real server). I would like it to run faster. Here is the
> code-what could I do to improve performance? Would this be faster if I
> ran it as a script task and used VBScript?
>
> Here is the code-please feel free to brutaly criticize. Thanks!
>
> SET QUOTED_IDENTIFIER OFF
> GO
> SET ANSI_NULLS OFF
> GO
>
> DECLARE @CallID int
> DECLARE @Timestamp datetime
> DECLARE @CallEventName varchar (80)
> DECLARE @TelsetLoginID varchar (16)
> DECLARE @AData varchar (40)
> DECLARE @PPlace varchar (40)
>
> DECLARE @Reporting_ID int
> DECLARE @TranCallType varchar(40)
>
> DECLARE @LastRecordCallID int
> DECLARE @LastRecordTimestamp datetime
>
> DECLARE Parse_Cleaned_CBC_Data CURSOR
> GLOBAL
> FOR
>
> SELECT * FROM CallByCallYesterdayCleaned ORDER BY CallID, [Timestamp]
>
> OPEN Parse_Cleaned_CBC_Data
>
> SELECT @Reporting_ID = 0
> SELECT @LastRecordCallID = -1
>
> WHILE @@FETCH_STATUS = 0
>
> BEGIN
> FETCH NEXT FROM Parse_Cleaned_CBC_Data INTO
> @CallID, @Timestamp, @CallEventName, @TelsetLoginID, @AData, @PPlace
>
> IF @CallID <> @LastRecordCallID OR DATEDIFF(s, @LastRecordTimestamp,
> @Timestamp) > 1800
> BEGIN
> SELECT @Reporting_ID = @Reporting_ID + 1
> INSERT INTO CallByCallYesterdayFinal (CallID, Reporting_ID,
> [Timestamp]) VALUES (@CallID, @Reporting_ID, @Timestamp)
>
>
> END
>
>
> IF @CallEventName = "EventType1" OR
> @CallEventName = "EventType2"
> BEGIN
> IF @AssociatedData = "NORM"
> BEGIN
> UPDATE CallByCallYesterdayFinal SET Tran_Call_Type = "FirstCall"
> WHERE Reporting_ID = @Reporting_ID
> END
>
> IF LEFT(@AData,6) = "TRANSF"
> BEGIN
> UPDATE CallByCallYesterdayFinal SET Tran_Call_Type = "TranCall"
> WHERE Reporting_ID = @Reporting_ID
> UPDATE CallByCallYesterdayFinal SET Tran_From_CallID =
> SUBSTRING(@AData, 22, 8)
> WHERE Reporting_ID = @Reporting_ID
> END
> END
>
>
> IF @CallEventName = "EventType3" OR
> @CallEventName = "EventType4"
>
> BEGIN
> UPDATE CallByCallYesterdayFinal SET Script = SUBSTRING(@Destination,
> 8, LEN(@PPlace))
> WHERE Reporting_ID = @Reporting_ID
> END
>
> IF @CallEventName = "EventType6" or @CallEventName = "EventType7"
> BEGIN
> UPDATE CallByCallYesterdayFinal SET Queue = SUBSTRING(@AData, 9,
> LEN(@AData))
> WHERE Reporting_ID = @Reporting_ID
> UPDATE CallByCallYesterdayFinal SET LoginID = @TelsetLoginID
> WHERE Reporting_ID = @Reporting_ID
> END
>
> IF @CallEventName = "EventType5"
> BEGIN
> UPDATE CallByCallYesterdayFinal SET Tran_CallID = SUBSTRING(@AData,
> 15, 8)
> WHERE Reporting_ID = @Reporting_ID
>
> END
>
>
> SELECT @LastRecordCallID = @CallID
> SELECT @LastRecordTimestamp = @Timestamp
>
> END
>
> /* Clean up our cursor*/
>
> CLOSE Parse_Cleaned_CBC_Data
> DEALLOCATE Parse_Cleaned_CBC_Data
> GO
> SET QUOTED_IDENTIFIER OFF
> GO
> SET ANSI_NULLS ON
> GO
>
  • Next message: David Portas: "Re: Performance Issues With Query"

    Relevant Pages

    • Help improve speed of this code
      ... Data Pump: Grabs a table from a Sybase database, ... writes this to SQL Table 2. ... DECLARE @Timestamp datetime ... UPDATE CallByCallYesterdayFinal SET Tran_Call_Type = "FirstCall" ...
      (microsoft.public.sqlserver)
    • Performance Issues With Query
      ... Data Pump: Grabs a table from a Sybase database, ... writes this to SQL Table 2. ... DECLARE @Timestamp datetime ... UPDATE CallByCallYesterdayFinal SET Tran_Call_Type = "FirstCall" ...
      (microsoft.public.sqlserver)
    • HELP in "Where" clause
      ... I would like to pass a stream of varchar as my "where" clause as follow. ... I can't get this work until I declare the SQL as varchar and then execute the query as follow: ...
      (microsoft.public.sqlserver.server)
    • Re: Using a variable to access a database
      ... You need to use dynamic SQL execution either using EXEC or sp_executesql ... declare @sql varchar ... declare @db varchar ...
      (microsoft.public.sqlserver.connect)
    • Re: HELP in "Where" clause
      ... you need dynamic SQL for that. ... "Abi" wrote in message ... > I would like to pass a stream of varchar as my "where" clause as follow. ... > DECLARE @SQL VARCHAR ...
      (microsoft.public.sqlserver.server)
    • Quantcast