Re: query datetime range, how to optimize

Tech-Archive recommends: Fix windows errors by optimizing your registry

From: Wayne Snyder (wayne.nospam.snyder_at_mariner-usa.com)
Date: 11/09/04 

Date: Tue, 9 Nov 2004 07:50:10 -0500

What happens when you take OFF the index hint
 "from pos_txn_log (index=pk_pos_txn_log) " 

-- 
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Andrew" <Andrew@discussions.microsoft.com> wrote in message
news:9A2A9227-8111-4788-B4DA-C96848AEF958@microsoft.com...
> Hi,
>
> My query 1 needs to narrow down a time range.  I index the column
> (pos_trandate_time, plus the other on the where clause), but the
performance
> still very slow, around 1 minutes. Wherease the query on date only is much
> faster. What should I do?
>
> --query 1, cost 99%
> select CONVERT(char(8), pos_trandate_time, 3) as tx_date, CONVERT(char(8),
> pos_trandate_time, 8) as tx_time,  pos_purchase_amt as tx_amt,
> pos_cashback_amt as tx_cashback_amt, pos_seq_no as tx_seq_no, pos_msg_type
> as tx_msg_type, pos_trans_code as tx_tran_type
> from pos_txn_log (index=pk_pos_txn_log)
> where pos_tid ='80007001'  and pos_trandate_time >= '2004-07-05 00:00:00'
> and pos_trandate_time <= '2004-07-05 23:59:59'
> order by pos_trandate_time
>
> -- query 2, cost %1
> select CONVERT(char(8), pos_trandate_time, 3) as tx_date, CONVERT(char(8),
> pos_trandate_time, 8) as tx_time,
> pos_purchase_amt as tx_amt, pos_cashback_amt as tx_cashback_amt,
pos_seq_no
> as tx_seq_no, pos_msg_type as tx_msg_type,
> pos_trans_code as tx_tran_type
> from pos_txn_log  where pos_tid ='80007001' and pos_rid ='11180007000'
and
> CONVERT(char(8), pos_settlementdate, 3) = '05/07/04'
> order by pos_trandate_time
>
>
> Thanks
> Wang

Relevant Pages

  • Re: How ">=" Work on Varchar field?
    ... Because my original Query was bit slow when used with order by. ... using that with Index hint, ... > Columnist, SQL Server Professional ... > Hi Tom, ...
    (microsoft.public.sqlserver.programming)
  • Re: CONTAINS performance
    ... That said, and with the query plan, I can start to give you more ... relational join in the context of the free-text optimization, ... SQL Server tables. ...
    (microsoft.public.sqlserver.fulltext)
  • Re: Indexing Service, Openquery and sp_executesql
    ... SQL Server version and sp are you running? ... > data from the indexing service catalog when pasted into ... > query analyzer, but failed when put against sp_executesql ... I would choose Microsoft Indexing ...
    (microsoft.public.sqlserver.fulltext)
  • Re: Problem using Access or Query Designer to run queries in SQL Serve
    ... >or Query Designer within Enterprise Manager, it works and I get data back. ... >ODBC Call Failed [ODBC SQL Server Driver] Timeout Expirederror in Access ... >[ODBC SQL Server Driver] Timeout Expired ...
    (microsoft.public.sqlserver.odbc)
  • Extreme performance issues (SQL Server 2000/ADO.NET/C#)
    ... This process runs very quickly if run through Query ... same exact stored procedures and views, run in the same exact order, through ... system that runs SQL Server (a 4-cpu Xeons system with 2gigs of physical ... When I execute these steps manually through query analyser,, ...
    (microsoft.public.dotnet.framework.adonet)