Make sure that SELECT INTO/Bulk copy is turned on for the database that your
temporary table is in. If you are using just one database and need
transaction log backups (and therefore can't turn SELECT INTO/bulk copy on),
create a new database just for staging this exported data.

Since your table has only one index and the major column of that index is
not part of your where clause, SQL Server has to check each of the 7 million
rows. If you added an index on LogTime the situation won't change as SQL 6.5
can't use an index and deal with SUBSTRING at the same time.

Is reversing the order of the columns in the clustered index possible? That
might allow SQL Server to eliminate some of the sorting it is doing, you'd
have to test it.

Beyond that, you are stuck doing the normal sort of things like making sure
that you have as much RAM as is feasible and that you disk layout is
optimal.

HTH,
Darin Strait, MS SQL Server Development and Administration
http://home.earthlink.net/~dstrait/professional/resume.htm


----- Original Message -----
From: "Stanley Dam" <StanleyDam@p...>
To: "sql language" <sql_language@p...>
Sent: Wednesday, May 09, 2001 12:20 AM
Subject: [sql_language] [SQL-language] Need Help on SQL execute Time out


> /------------------------------------------------------------------
> Problem
>    SQL Time out when executes the following SQL statement
>
>    Works fine with a few hundred thousand of records.
>
> /------------------------------------------------------------------
> Operation
>    I am using VB with ADO to archive data on the SQL Server to csv file
> every night
>
>    This application copies all records with the logtime older than 1 month
> into a temp table
>    then calls the bcp (Bulk copy program) to export the data to a csv
file.
> Once the operation
>    completed successfully delete all records with logtime older than 1
> month.
>
>
>    Coding in VB
>
>    ConSQLServer.Execute "Insert into TempHistory (Tag,LogTime,Value) " & _
>                         "Select Tag,LogTime,Value From tbLog Where " & _
>                      "WHERE SubString(LogTime,1,8) <= '" & sDate & "'
ORDER
> BY SubString(LogTime,1,6) ",
>
>    Where SDate = date in yyyymmdd
>
>    (Notes : Using SQL SERVER 6.5 BCP only works on a Table but not a query
> set - )
>
> /-------------------------------------------------------------------
> Environment
> SQL Server 6.5
>
> The table namely "tbLog" has 3 columns
>
> Tag   varchar(50)
> LogTime  varchar(25)         ' This field store the date time in this
format
> yyyymmdd hh:mm:ss.xxx  20010501 01:15:18.500
> Value   varchar(255)
>
> A Cluster Index is created on field Tag and LogTime
>
> Currently about 7 million records in the table tblog
>
> /--------------------------------------------------------------------
> Would really appreciated for any input or better approach on the solution.
>
>