December 11, 2006 at 4:37 pm
The query, select * from tbl_ORD_CUST where c_order_id = x (ddl below)
it's taking 4-8 seconds to return (4408526 total rows). A count (*) returns in subsecond!! If try to use a hint against the index on the computed column, I get an error:
Query processor could not produce a query plan because of the hints defined in this query. Resubmit the query without specifying any hints and without using SET FORCEPLAN.
here's the ddl:
CREATE TABLE [tbl_ORD_CUST] (
[ID] [bigint] NOT NULL ,
[FIRST_NAME] [varchar] (20) NULL ,
[LAST_NAME] [varchar] (20) NULL ,
[ADDRESS1] [varchar] (20) NULL ,
[ADDRESS2] [varchar] (20) NULL ,
[CITY] [varchar] (20) NULL ,
[STATE] [varchar] (2) NULL ,
[ZIP] [varchar] (10) NULL ,
[COUNTY] [varchar] (20) NULL ,
[DAY_PHONE] [varchar] (10) NULL ,
[EVENING_PHONE] [varchar] (10) NULL ,
[COUNTRY_ID] [varchar] (2) NULL ,
[VERSION] [int] NOT NULL ,
[EMAIL_ADDRESS] [varchar] (100) NULL ,
[CUSTOMER_TYPE] [varchar] (1) NULL ,
[c_ORDER_ID] AS (convert(bigint,left([id],(len([id]) - 2)))) ,
CONSTRAINT [PK_tbl_ORD_CUST] PRIMARY KEY CLUSTERED
(
[ID]
) WITH FILLFACTOR = 90 ON [DATA]
) ON [DATA]
GO
if exists (select * from dbo.sysindexes where name = N'idx_order_id' and id = object_id(N'[dbo].[tbl_ORD_CUST]'))
drop index [dbo].[tbl_ORD_CUST].[idx_order_id]
GO
set ANSI_PADDING,ANSI_WARNINGS,CONCAT_NULL_YIELDS_NULL,ARITHABORT,QUOTED_IDENTIFIER,ANSI_NULLS on
GO
set NUMERIC_ROUNDABORT off
GO
CREATE INDEX [idx_order_id] ON [dbo].[tbl_ORD_CUST]([c_ORDER_ID]) ON [IDX]
GO
set arithabort OFF
GO
set numeric_roundabort OFF
GO
set quoted_identifier OFF
GO
December 11, 2006 at 5:20 pm
4 to 8 seconds to return 4.4M rows isn't all that surprising (that's pretty fast, actually). Your query has to transfer huge amounts of data to the client (800-1100+MB if my calculations are correct)
add this to the beginning of your query
SET SHOWPLAN_TEXT ON
then run the batch and post what you see in the message window please.
Thanks
SQL guy and Houston Magician
December 11, 2006 at 5:21 pm
PS: Thank you for posting the DDL - All too often forgotten by posters
SQL guy and Houston Magician
December 11, 2006 at 5:28 pm
now, the query is using they index. I don't get it. and the response is sub second. you calc on the amount of data is way off for just one row.
December 11, 2006 at 5:33 pm
My calc was a rough guess for 4.4M rows (I may have misread your earlier post).
Let me clarify this: when it was taking 4-8 seconds, how many rows was it returning?
As for the index being unused, there are a lot of factors that can affect this. Are your statistics up to date, what is the cost of the bookmark lookup vs the cost of the scan, how selective is the index etc.
SQL guy and Houston Magician
December 11, 2006 at 5:39 pm
It was for 1 row, at most it would have returned 8 rows. The statistics could have been out of date since we just loaded the data (bulk). The Index is generally selective, there are, the data averages 3 rows per c_order_id or 1.4M distinct values over 4.4M records.
December 11, 2006 at 5:40 pm
4.4 mil rows in 4-8 seconds is very fast ... how many total does this table contains...
MohammedU
Microsoft SQL Server MVP
December 11, 2006 at 5:50 pm
it was only 1 row. the total table size is 4.4M. It now returns sub second.
December 11, 2006 at 8:01 pm
> select * from tbl_ORD_CUST where c_order_id = x
It must be @x?
And what is the datatype of @x?
_____________
Code for TallyGenerator
December 12, 2006 at 9:00 am
That's pretty likely, especially since you say it's using the index now.
SQL guy and Houston Magician
December 12, 2006 at 5:15 pm
If you normally load the data (bulk), the index statistics would normally be off. You can use ...
select * from tbl_ORD_CUST (Index(idx_order_id)) where c_order_id = @x
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply