I am afraid I am here again with a problem (I seem to create more help
requests than answers these days). I have the following table (which is not
my design!).
[Messagetpye] [varchar] (50) NULL ,
[Sourcetype] [varchar] (50) NULL ,
[CallID] [varchar] (50) NULL ,
[Devicetype] [varchar] (50) NULL ,
[DeviceID] [varchar] (50) NULL ,
[Starttimestamp] [datetime] NOT NULL ,
[Direction] [varchar] (50) NULL ,
[AdvisorID] [varchar] (50) NULL ,
[CustomerID] [varchar] (50) NULL ,
[ActivityID] [varchar] (50) NULL ,
[AuditTrail] [varchar] (8000) NULL
I have set Indexing on
StartTimeStamp as Clustered
AdvisorID
CustomerID
ActivityID
If I do a simple SQL Statement like.
select top 100 * from tbl_audittrail where advisorid like '%LH%'
I get a 10 second response and 97% cost on the index scanning 390,610
however if I do..
select top 50 * from tbl_audittrail where advisorid like '%LH%' and
starttimestamp >'03 august 2007'
I get a 120 second response and 97% cost on the index scanning 4,325,334
Is there any way to configure this in a better way to get a quicker response
on the 2nd Query as the web front end I have build always has to use the
starttimestamp along with one of the indexed fields in its searches.
Any help is greatly appreciated.
Stumpy
--
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200709/1Hi
First of all what is the version are you using? It is important because MS
has added in SQL Server 2005 INCLUDE operator for indexes, see BOL
Secondly, what kind of indexes you are created? Is it Clustered or
NonClusterd?
> select top 100 * from tbl_audittrail where advisorid like '%LH%'
Don't use TOP clause without specify ORDER BY clause as you may geg
unordered result. Moreover , with the above query SQL Server will not use an
index as you provide '%%' .Do you really need to return all columns? What if
you run
SELECT AdvisorID,CustomerID FROM tbl WHERE advisorid like 'LH%' ,does it
run faster?
> select top 50 * from tbl_audittrail where advisorid like '%LH%' and
> starttimestamp >'03 august 2007'
Again , do you have an index on starttimestamp ? If you do try run this
query as
select top 50 * from tbl_audittrail where advisorid like '%LH%' and
starttimestamp >='20070803' AND starttimestamp < DATEADD(d,1,getdate())
ORDER BY .....
"stumpy_uk via SQLMonster.com" <u15773@.uwe> wrote in message
news:77c0b1470e597@.uwe...
>I am afraid I am here again with a problem (I seem to create more help
> requests than answers these days). I have the following table (which is
> not
> my design!).
> [Messagetpye] [varchar] (50) NULL ,
> [Sourcetype] [varchar] (50) NULL ,
> [CallID] [varchar] (50) NULL ,
> [Devicetype] [varchar] (50) NULL ,
> [DeviceID] [varchar] (50) NULL ,
> [Starttimestamp] [datetime] NOT NULL ,
> [Direction] [varchar] (50) NULL ,
> [AdvisorID] [varchar] (50) NULL ,
> [CustomerID] [varchar] (50) NULL ,
> [ActivityID] [varchar] (50) NULL ,
> [AuditTrail] [varchar] (8000) NULL
> I have set Indexing on
> StartTimeStamp as Clustered
> AdvisorID
> CustomerID
> ActivityID
> If I do a simple SQL Statement like.
> select top 100 * from tbl_audittrail where advisorid like '%LH%'
> I get a 10 second response and 97% cost on the index scanning 390,610
> however if I do..
> select top 50 * from tbl_audittrail where advisorid like '%LH%' and
> starttimestamp >'03 august 2007'
> I get a 120 second response and 97% cost on the index scanning 4,325,334
> Is there any way to configure this in a better way to get a quicker
> response
> on the 2nd Query as the web front end I have build always has to use the
> starttimestamp along with one of the indexed fields in its searches.
> Any help is greatly appreciated.
> Stumpy
> --
> Message posted via SQLMonster.com
> http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200709/1
>|||Uri Dimant wrote:
>Hi
>First of all what is the version are you using? It is important because MS
>has added in SQL Server 2005 INCLUDE operator for indexes, see BOL
>Secondly, what kind of indexes you are created? Is it Clustered or
>NonClusterd?
>> select top 100 * from tbl_audittrail where advisorid like '%LH%'
>Don't use TOP clause without specify ORDER BY clause as you may geg
>unordered result. Moreover , with the above query SQL Server will not use an
>index as you provide '%%' .Do you really need to return all columns? What if
>you run
>SELECT AdvisorID,CustomerID FROM tbl WHERE advisorid like 'LH%' ,does it
>run faster?
>> select top 50 * from tbl_audittrail where advisorid like '%LH%' and
>> starttimestamp >'03 august 2007'
>Again , do you have an index on starttimestamp ? If you do try run this
>query as
> select top 50 * from tbl_audittrail where advisorid like '%LH%' and
> starttimestamp >='20070803' AND starttimestamp < DATEADD(d,1,getdate())
>ORDER BY .....
>>I am afraid I am here again with a problem (I seem to create more help
>> requests than answers these days). I have the following table (which is
>[quoted text clipped - 39 lines]
>> Stumpy
Uri,
Its SQL 2000 and the datetime was a Clustered Index.
However you have answered my question as soon as I took the * Away and added
the order by its 0 - 5 seconds for each of the queries...thanks again for all
your help
Cheers
Lee
--
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200709/1
