Sign in
 
Home20122008 R2200820052000LibraryForums
SQL Server >
> 
> 
Is there a way to speed up this use of SQL Server 2000

Answered Is there a way to speed up this use of SQL Server 2000

  • Wednesday, February 13, 2013 7:18 PM
     
     
    Sign In to Vote
    0

    With the dates in the WHERE clause here, 5,100 records are selected.  It takes the function
    below 3 minutes to process them.
    With the actual date range desired, 191,000 records are selected.

    A stored procedure, running at the server would definitely be very much faster. However, the table being read is in a different database on a different server than the database than the database of the two tables it is inserting into.  The procedure would require a linked server between the two, which we are having trouble getting because IT is concerned with the security.

    Does anyone see a way to make this run significantly faster?

    SELECT  HIST_ROW_ID,  HIST_CREATE_DTM,  HIST_LOG_ACT_CD,  HIST_IMAGE_CD,  HIST_TERM_ID, 
    GRGR_CK,  GRGR_ID,  GRGR_STS,  GRGR_TERM_DT,  HIST_ROW_ID,  GRGR_RENEW_MMDD 
     FROM [server].[database].audit.CMC_GRGR_GROUP WITH (NOLOCK)  
    WHERE   HIST_CREATE_DTM BETWEEN '12/21/2012' AND '1/1/2013'  "

    [code]
    Public Function BuildGroupGRGRTERM() As Boolean

        Dim ocmd As New SqlCommand
        Dim GRGRcmd As New SqlCommand
        Dim TERMcmd As New SqlCommand

        Dim drPRptg As System.Data.SqlClient.SqlDataReader
        Dim drARS As System.Data.SqlClient.SqlDataReader
        Dim cnString As String = gConnectionString
        Dim cnARS As New SqlConnection(cnString)

        Dim ConnectionString As String
        Dim SQLCmd As String
        Dim ActualRecords As Integer
        Dim NumColumns As Integer

        Try

          ConnectionString = "Integrated Security=SSPI;Persist Security Info=False;"
          ConnectionString &= "Data Source=" & gPRptgDBServer & ";Initial Catalog=" & gPRptgDatabase & ";Connection Timeout=1200;"

          Dim cnPRptg As New SqlConnection(ConnectionString)


          SQLCmd = FormGroupGRGRSql()

          If gDatabase = "DEV" Then
            Debug.Print("SQLCmd: " & Environment.NewLine & SQLCmd)
            '  MessageBox.Show(SQLCmd)
          End If

          Dim StartTime As String = CStr(Now)

          With ocmd
            .Connection = cnPRptg
            .CommandText = SQLCmd
          End With

          cnPRptg.Open()
          drPRptg = ocmd.ExecuteReader
          NumColumns = drPRptg.FieldCount

          With GRGRcmd
            .Connection = cnARS
            .CommandType = CommandType.StoredProcedure
            .CommandText = "spSampGroupGRGRD"
          End With
          cnARS.Open()
          drARS = GRGRcmd.ExecuteReader
          cnARS.Close()
          With GRGRcmd
            .Connection = cnARS
            .CommandType = CommandType.StoredProcedure
            .CommandText = "spSampGroupGRGRI"
          End With

          With TERMcmd
            .Connection = cnARS
            .CommandType = CommandType.StoredProcedure
            .CommandText = "spSampGroupTERMD"
          End With
          cnARS.Open()
          drARS = TERMcmd.ExecuteReader
          cnARS.Close()
          With TERMcmd
            .Connection = cnARS
            .CommandType = CommandType.StoredProcedure
            .CommandText = "spSampGroupTERMI"
          End With

          cnARS.Open()

          ActualRecords = 0

          With GRGRcmd
            .Parameters.Add("@HistRowID", SqlDbType.VarChar, 10)
            .Parameters.Add("@HistCreateDTM", SqlDbType.DateTime)
            .Parameters.Add("@HistLogActCD", SqlDbType.Char, 1)
            .Parameters.Add("@HistImageCD", SqlDbType.Char, 1)
            .Parameters.Add("@HistTermID", SqlDbType.VarChar, 20)
            .Parameters.Add("@GRGRCk", SqlDbType.Int)
            .Parameters.Add("@GRGRId", SqlDbType.VarChar, 8)
            .Parameters.Add("@GRGRSts", SqlDbType.VarChar, 2)
            .Parameters.Add("@GRGRTermDT", SqlDbType.DateTime)
            .Parameters.Add("@GRGRRenewMMDD", SqlDbType.SmallInt)
          End With


          With TERMcmd
            .Parameters.Add("@GRGRCk", SqlDbType.Int)
          End With

          While drPRptg.Read()
            ActualRecords += 1

            With GRGRcmd
              .Parameters("@HistRowID").Value = drPRptg.Item("HIST_ROW_ID")
              .Parameters("@HistCreateDTM").Value = CDate(drPRptg.Item("HIST_CREATE_DTM")).ToShortDateString
              .Parameters("@HistLogActCD").Value = drPRptg.Item("HIST_LOG_ACT_CD")
              .Parameters("@HistImageCD").Value = drPRptg.Item("HIST_IMAGE_CD")
              .Parameters("@HistTermID").Value = drPRptg.Item("HIST_TERM_ID")
              .Parameters("@GRGRCk").Value = drPRptg.Item("GRGR_CK")
              .Parameters("@GRGRId").Value = drPRptg.Item("GRGR_ID")
              .Parameters("@GRGRSts").Value = drPRptg.Item("GRGR_STS")
              .Parameters("@GRGRTermDT").Value = CDate(drPRptg.Item("GRGR_TERM_DT")).ToShortDateString
              .Parameters("@GRGRRenewMMDD").Value = drPRptg.Item("GRGR_RENEW_MMDD")
            End With
            GRGRcmd.ExecuteNonQuery()


            If UCase(CStr(drPRptg.Item("GRGR_STS"))) = "TM" Then
              With TERMcmd
                .Parameters("@GRGRCk").Value = drPRptg.Item("GRGR_CK")
              End With
              TERMcmd.ExecuteNonQuery()
            End If

          End While

          cnPRptg.Close()
          cnARS.Close()

          MessageBox.Show("Started: " & StartTime & "   End Time: " & Now & "Records Read: " & ActualRecords.ToString)

          BuildGroupGRGRTERM = True

          If ActualRecords > 0 Then
            Exit Function

          Else
            If Not gSilentMode Then
              MessageBox.Show("No Groups from ProdReporting Data Found", _
                              Application.ProductName & " No Group ProdReporting Data Found")
            End If
            InformationHandler("No Groups from ProdReporting Data Found", "")
            BuildGroupGRGRTERM = False
            Exit Function
          End If

        Catch ex As Exception
          ErrorHandler("DataAccessModule.BuildGroupGRGRTERM", "prodreporting connection string:" _
                       & Environment.NewLine & ConnectionString _
                       & Environment.NewLine & "SQLCmd: " & SQLCmd, ex)
        End Try

      End Function
    [/code]

    -------------------------------------

    After the select, use drPRptg to read one record at a time.
    Use GRGRcmd to insert records into a table using a stored procedure. Conditionally use TERMcmd to write to a second table with another stored procedure.

    Stored procedures:
    [code]

     
    SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER OFF
    GO
     
    if exists (select * 
      from dbo.sysobjects 
      where id = object_id(N'[dbo].[spSampGroupGRGRI]') and 
                 OBJECTPROPERTY(id, N'IsProcedure') = 1)
        drop procedure [dbo].[spSampGroupGRGRI]
    GO
     
    create procedure [dbo].[spSampGroupGRGRI]
        (
        @HistRowID                numeric(10,0),
      @HistCreateDTM        datetime,
        @HistLogActCD         char(1),
        @HistImageCD          char(1),
        @HistTermID           varchar(20),
        @GRGRCk               int,
        @GRGRId               varchar(8),
        @GRGRSts              varchar(2),
        @GRGRTermDT           datetime,
        @GRGRRenewMMDD        smallint
        )
     
    AS INSERT INTO [dbo].[SampGroupGRGR] 
         (
        HIST_ROW_ID,
      HIST_CREATE_DTM,
        HIST_LOG_ACT_CD,
        HIST_IMAGE_CD,
        HIST_TERM_ID,
        GRGR_CK,
        GRGR_ID,
        GRGR_STS,
        GRGR_TERM_DT,
        GRGR_RENEW_MMDD
        ) 
     
    VALUES 
        (
        @HistRowID,
      @HistCreateDTM,
        @HistLogActCD,
        @HistImageCD,
        @HistTermID,
        @GRGRCk,
        @GRGRId,
        @GRGRSts,
        @GRGRTermDT,
        @GRGRRenewMMDD
        )
    GO

    ----------------------------------------
     
     
    SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER OFF
    GO
     
    if exists (select * 
      from dbo.sysobjects 
      where id = object_id(N'[dbo].[spSampGroupTERMI]') and 
                 OBJECTPROPERTY(id, N'IsProcedure') = 1)
        drop procedure [dbo].[spSampGroupTERMI]
    GO
     
    create procedure [dbo].[spSampGroupTERMI]
        (
        @GRGRCk               int
        )
     
    AS INSERT INTO [dbo].[SampGroupTERM] 
         (
        GRGR_CK
        ) 
     
    VALUES 
        (
        @GRGRCk
        )
    GO
    [/code]

    MLarsB

     

All Replies

  • Wednesday, February 13, 2013 7:21 PM
     
     
    Sign In to Vote
    0
    I am selecting from one database/server which are different from the database/server I am inserting to.

    MLarsB

     
  • Wednesday, February 13, 2013 7:58 PM
     
     Answered
    Sign In to Vote
    0

    Extended support for SQL 2000 is ending soon. Please consider upgrading your SQL 2000 instance to SQL 2008 R2

    http://blogs.msdn.com/b/sqlagent/archive/2013/01/22/sql-server-2000-extended-support-for-sql-server-2000-ending-soon.aspx

    Thanks, Sethu Srinivasan [MSFT] SQL Server http://blogs.msdn.com/sqlagent -------------------------------------------------------------------------------- This posting is provided "AS IS" with no warranties, and confers no rights. Please remember to click "Mark as Answer" and "Vote as Helpful" on posts that help you. This can be beneficial to other community members reading the thread.

     
  • Wednesday, February 13, 2013 8:15 PM
    Moderator
     
      Has Code
    Sign In to Vote
    0
     
    --You can move your dotnet code directly into your stored procedure in one run

create procedure [dbo].[spSampGroupGRGRI_Combined]
@startDate datetime='12/21/2012', 
@endDate datetime= '1/1/2013' 
AS
BEGIN

Begin
 INSERT INTO [dbo].[SampGroupGRGR]  
     (  HIST_ROW_ID, 
  HIST_CREATE_DTM, 
    HIST_LOG_ACT_CD, 
    HIST_IMAGE_CD, 
    HIST_TERM_ID, 
    GRGR_CK, 
    GRGR_ID, 
    GRGR_STS, 
    GRGR_TERM_DT, 
    GRGR_RENEW_MMDD 
    )  
  
SELECT HIST_ROW_ID,
HIST_CREATE_DTM,
HIST_LOG_ACT_CD,
HIST_IMAGE_CD,
HIST_TERM_ID,GRGR_CK,
GRGR_ID,
GRGR_STS,
GRGR_TERM_DT,
GRGR_RENEW_MMDD

FROM   [server].[database].audit.CMC_GRGR_GROUP 
WHERE  HIST_CREATE_DTM >= @startDate AND HIST_CREATE_DTM < endDate 

End

Begin
INSERT INTO [dbo].[SampGroupTERM] (GRGR_CK )  
  
SELECT GRGR_CK FROM   [server].[database].audit.CMC_GRGR_GROUP 
WHERE  GRGR_STS='TM'
End

END

     
  • Wednesday, February 13, 2013 8:20 PM
     
     
    Sign In to Vote
    0

    How about copying the query's result set to the target database (in a staging table) and then run a stored procedure in the target database to process all rows at once?

    Use batch tools to import the data in the staging table. If the target database is running SQL Server 2000, you can use BCP for that.

    Gert-Jan

     
  • Wednesday, February 13, 2013 9:07 PM
     
     
    Sign In to Vote
    0
    I cannot use a stored procedure because security does not want a linked server from my database, where my stored procedure would be running,  to another database on another server.

    MLarsB

     
  • Wednesday, February 13, 2013 9:21 PM
    Moderator
     
     
    Sign In to Vote
    0
    I cannot use a stored procedure because security does not want a linked server from my database, where my stored procedure would be running,  to another database on another server.

    MLarsB

    But you are using stored procedure now. Unless I miss something here.
     
  • Wednesday, February 13, 2013 9:33 PM
     
     
    Sign In to Vote
    0

    No, I am not using a stored procedure.  I build a string in VB with the SQL SELECT statement in it.  I use a connection string which contains the other database's name and server.  Then I execute the string command.

    Then I loop through the records returned by the select:  While drPRptg.Read()

    and insert to the two tables.

    --------------------------------------

    With ocmd
            .Connection = cnPRptg
            .CommandText = SQLCmd
     End With

          cnPRptg.Open()
          drPRptg = ocmd.ExecuteReader

    While drPRptg.Read()

    MLarsB

     
  • Wednesday, February 13, 2013 9:52 PM
    Moderator
     
     
    Sign In to Vote
    0

    What are these in your code:

    spSampGroupGRGRD
    spSampGroupGRGRI
    spSampGroupTERMD
    spSampGroupTERMI

       .CommandType = CommandType.StoredProcedure

    If you decide that you don't run stored procedure any more, you still can move your code with the query I showed you.

    If you enjoy the loop in your code, I cannot help any further.

     
  • Wednesday, February 13, 2013 10:02 PM
     
     
    Sign In to Vote
    0

    I read the data from another database with a connection and executing it, without a stored procedure.  That is what I was refering to as not using a stored procedure.

    Those stored procedures are to delete and then do inserts in the two tables I am writing to.  They run only in my database; they do not reach out to the other database.

    If I could use a stored procedure to select from the other database I would not need these; it would insert directly into these tables.

    Sorry, I do not know what query you showed me.

    MLarsB

     
  • Wednesday, February 13, 2013 10:24 PM
     
     
    Sign In to Vote
    0

    Now I see the code you sent.  The DBA's tell me that a stored procedure running in my database cannot read from a different  [server].[database] without a linked server, which security does not want to create.

    Therefore a stored proc cannot be used here.

    MLarsB

     
  • Wednesday, February 13, 2013 10:41 PM
    Moderator
     
     
    Sign In to Vote
    0
    Can you try to grab the query I posted and use it inline without stored procedure? You still need access right to make the database call to another server for your inline query.
     
  • Wednesday, February 13, 2013 11:53 PM
     
     
    Sign In to Vote
    0

    I think I get what you mean.

    So this would be one SQL command in the VB program, right?

    Begin
    INSERT INTO [server1].[database1] [dbo].[SampGroupTERM] (GRGR_CK ) 
     
    SELECT GRGR_CK FROM   [server2].[database2].audit.CMC_GRGR_GROUP
    WHERE  GRGR_STS='TM'
    End

    To access a database, I have to have a connection to it.  If the INSERT and the SELECT are both in one command this would not work.  I can only specify one database connection for executing the command.  The insert and select are for two different servers, needing two connection strings.  I set the connection to server2.database2.

    When I tried it I got the following error:

    Error: 5 - Could not find server 'server1' in sys.servers

    Does anyone else have a different view on what I said?

    MLarsB