Performance hit

Hi,
We have a table with approx 3.5 million rows. Queries against it are slow.
One particular query which does a LIKE search on an invoice number takes 10
seconds to return 5 rows. There is a datatype conversion issue in the LIKE
search but still the queries agains this are slow. If i split index and data
into seperate files would it help. How can i split the data in a table to
seperate files?
Thanks
NavinHi.,
Before splitting the Non clusteredd Index and data into seperate files try
to do these:-
1. Check your select statement is using an index using Execution plan
2. Execute DBCC SHOWCONTIG and see the table is fragmented. If fragmented
remove the fragmentation by DBCC DBREINDEX
3. Run the Update statistics on the tables in FROM Clause
Thanks
Hari
MCDBA
"Navin Vishnu" <navinittiyamparampadh@.comcast.net> wrote in message
news:#QSDz$RdEHA.4092@.TK2MSFTNGP10.phx.gbl...
> Hi,
> We have a table with approx 3.5 million rows. Queries against it are slow.
> One particular query which does a LIKE search on an invoice number takes
10
> seconds to return 5 rows. There is a datatype conversion issue in the LIKE
> search but still the queries agains this are slow. If i split index and
data
> into seperate files would it help. How can i split the data in a table to
> seperate files?
> Thanks
> Navin
>|||Hi Navin
>There is a datatype conversion issue in the LIKE search<
Indexing cannot help queries that do not have SARG'able (Search
Argument'able) predicates. Eg, if you use 'LIKE %xxx' in your WHERE clause,
no index can possibly help because index values are read left to right &
having the wildcard at the beginning means any row can potentially qualify
for the results and needs to be read.
You should post the table definition, the index definition & the query so we
have a meaningful chance of advising whether any specific index would be
useful.
I'd also suggest that you look into using the Index Tuning Wizard as it is
very good at recommending indexes.
Regards,
Greg Linwood
SQL Server MVP
"Navin Vishnu" <navinittiyamparampadh@.comcast.net> wrote in message
news:%23QSDz$RdEHA.4092@.TK2MSFTNGP10.phx.gbl...
> Hi,
> We have a table with approx 3.5 million rows. Queries against it are slow.
> One particular query which does a LIKE search on an invoice number takes
10
> seconds to return 5 rows. There is a datatype conversion issue in the LIKE
> search but still the queries agains this are slow. If i split index and
data
> into seperate files would it help. How can i split the data in a table to
> seperate files?
> Thanks
> Navin
>|||Also, Please post your DDL and DML to get more accurate responses.
--
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
"Navin Vishnu" <navinittiyamparampadh@.comcast.net> wrote in message
news:%23QSDz$RdEHA.4092@.TK2MSFTNGP10.phx.gbl...
> Hi,
> We have a table with approx 3.5 million rows. Queries against it are slow.
> One particular query which does a LIKE search on an invoice number takes
10
> seconds to return 5 rows. There is a datatype conversion issue in the LIKE
> search but still the queries agains this are slow. If i split index and
data
> into seperate files would it help. How can i split the data in a table to
> seperate files?
> Thanks
> Navin
>|||The table is appromimatly 4 million rows.
Here's the table structure
CREATE TABLE [dbo].[Ar] (
[BRANCH_NUMBER] [int] NOT NULL ,
[PATIENT_NUMBER] [int] NOT NULL ,
[BILL_TYPE] [char] (2) NOT NULL ,
[CARRIER_NUMBER] [int] NOT NULL ,
[INVOICE_NUMBER] [float] NOT NULL ,
[ORDER_NUMBER] [int] NULL ,
[MB_PREFIX] [int] NULL ,
[ORDER_TERMS] [int] NULL ,
[XO_ACCOUNT_NUMBERS] [char] (25) NULL ,
[XO_SOURCE_ACCT_NUMBER] [int] NULL ,
[DELIVERY_DATE] [smalldatetime] NULL ,
[SERVICE_DATE] [smalldatetime] NULL ,
[RP_OR_S] [char] (1) NULL ,
[N_OR_U] [char] (1) NULL ,
[PRODUCT_NUMBER] [char] (20) NULL ,
[QUANTITY_DELIVERED] [numeric](12, 2) NULL ,
[ITEM_PRICE] [money] NULL ,
[PROCEDURE_CODE] [char] (6) NULL ,
[ALLOWABLE] [money] NULL ,
[AMOUNT] [money] NULL ,
[ITEM_BALANCE] [money] NULL ,
[_20_PERCENT_INVOICE] [char] (1) NULL ,
[INVOICE_COST] [money] NULL ,
[TAX_CODE] [char] (5) NULL ,
[ITEM_TAX] [money] NULL ,
[SALESPERSON_NUMBER] [int] NULL ,
[REFERRAL_SOURCE_NUMBER] [numeric](12, 2) NULL ,
[BILLED_AMOUNT] [money] NULL ,
[DATE_RESUBMITTED] [smalldatetime] NULL ,
[DATE_RECORD_CREATED] [smalldatetime] NULL ,
[INVOICE_DATE] [smalldatetime] NULL ,
[TO_DATE] [smalldatetime] NULL ,
[CLAIM_STATUS] [char] (20) NULL ,
[CAPPED_RENTAL_MONTH] [smallint] NULL ,
[ENTERAL_TYPE] [char] (2) NULL ,
[MODIFIER_1] [char] (2) NULL ,
[MODIFIER_2] [char] (2) NULL ,
[SPECIAL_MODIFIER_1] [char] (2) NULL ,
[SPECIAL_MODIFIER_2] [char] (2) NULL ,
[PLACE_OF_SERVICE_CODE_E] [char] (2) NULL ,
[PLACE_OF_SERVICE_CODE_P] [char] (2) NULL ,
[SERVICE_TYPE] [char] (2) NULL ,
[MEDIA_TYPE] [char] (1) NULL ,
[ASSIGNED] [char] (1) NULL ,
[PA_NUMBER] [char] (20) NULL ,
[PA_REQUIRED] [char] (1) NULL ,
[HOW_MANY_REFILLS] [int] NULL ,
[REFILLS_ALLOWED] [int] NULL ,
[DOCTOR_NUMBER] [int] NULL ,
[ORIG_CO_INSURANCE] [money] NULL ,
[ORIG_PRIMARY_PAID] [money] NULL ,
[ORIG_DEDUCTIBLE] [money] NULL ,
[DATE_AR_INIT_ENTERED] [smalldatetime] NULL ,
[AR_INIT_USER] [char] (30) NULL ,
[SERVICE_TERMINATE_DATE] [smalldatetime] NULL ,
[PRIMARY_PAID_DATE] [smalldatetime] NULL ,
[SERVICE] [char] (6) NULL ,
[REVENUE_CODE] [char] (3) NULL ,
[COUNTY_CODE] [char] (4) NULL ,
[BILL_CODE] [numeric](12, 2) NULL ,
[MEDICAID_RESUB_CODE] [char] (15) NULL ,
[ORIGINAL_RE_NO] [char] (30) NULL ,
[COB] [char] (3) NULL ,
[EMEVS] [char] (20) NULL ,
[PRODUCT_DESCRIPTION] [char] (30) NULL ,
[BILL_UNITS] [numeric](12, 2) NULL ,
[ORIG_ALLOWABLE] [money] NULL ,
[ORIGINAL_RE_NO_2] [char] (15) NULL ,
[RECORD_STATUS] [char] (1) NULL ,
[TherapyCode] [int] NULL ,
[ArType] [char] (1) NULL ,
[IvRxNumber] [int] NULL ,
[IvMixingNumber] [int] NULL ,
[EMPLOYEE_NUMBER] [numeric](12, 2) NULL ,
[PREBILLED] [char] (1) NULL ,
[FINANCE_CHARGE] [smallmoney] NULL ,
[LAST_FIN_CHRG_DATE] [smalldatetime] NULL ,
[CLAIM_MSG_1] [char] (6) NULL ,
[CLAIM_MSG_2] [char] (6) NULL ,
[CLAIM_MSG_3] [char] (6) NULL ,
[CLAIM_MSG_4] [char] (6) NULL ,
[CLAIM_MSG_5] [char] (6) NULL ,
[NumberOfDays] [int] NULL ,
[BillAmount_Modified] [char] (1) NULL ,
[Their_PO_Number] [char] (20) NULL ,
[TherapyException] [char] (1) NULL ,
[SERIAL_NUMBERS] [text] NULL ,
[BILLING_TYPE] [char] (1) NULL ,
[CONTRACT_CODE] [char] (10) NULL ,
[IV_BU_Qty] [float] NULL ,
[CAPITATED] [char] (1) NULL ,
[IsMayo] [varchar] (1) NULL ,
[DaysRangeRental] [varchar] (1) NULL ,
[StartRentalDay] [int] NULL ,
[EndRentalDay] [int] NULL ,
[InvBatchID] [char] (20) NULL ,
[Capitated_Bill] [char] (1) NULL ,
[IsCoPay] [char] (1) NULL ,
[ITEM_PRICE_Old] [money] NULL ,
[ALLOWABLE_Old] [money] NULL ,
[BILLED_AMOUNT_Old] [money] NULL ,
[AMOUNT_Old] [money] NULL ,
[ORIG_PRIMARY_PAID_Old] [money] NULL ,
[ORIG_CO_INSURANCE_Old] [money] NULL ,
[ORIG_DEDUCTIBLE_Old] [money] NULL ,
[Cost_Center_Code] [char] (5) NULL ,
[Special_Price] [char] (1) NULL ,
[Ar_At_Allowed] [char] (1) NULL ,
[DeliveryNumber] [int] NULL ,
[NumberOfDaysAdmin] [int] NULL ,
[UserName] [char] (20) NULL ,
[ORDER_NUMBER_SUFFIX] [int] NULL ,
[AR_Note] [char] (40) NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
ALTER TABLE [dbo].[Ar] WITH NOCHECK ADD
CONSTRAINT [PK__Ar__5654B625] PRIMARY KEY CLUSTERED
(
[BRANCH_NUMBER],
[PATIENT_NUMBER],
[BILL_TYPE],
[CARRIER_NUMBER],
[INVOICE_NUMBER]
) WITH FILLFACTOR = 90 ON [PRIMARY]
GO
CREATE INDEX [BillTypeIndex] ON [dbo].[Ar]([BRANCH_NUMBER], [BILL_TYPE],
[ArType]) WITH FILLFACTOR = 90 ON [PRIMARY]
GO
CREATE INDEX [ByBillTypePatient] ON [dbo].[Ar]([BRANCH_NUMBER],
[BILL_TYPE], [PATIENT_NUMBER], [ArType]) WITH FILLFACTOR = 90 ON [PRIMARY]
GO
CREATE INDEX [ByCarrierInvoice] ON [dbo].[Ar]([BRANCH_NUMBER],
[CARRIER_NUMBER], [BILL_TYPE], [INVOICE_NUMBER], [ArType]) WITH FILLFACTOR
= 90 ON [PRIMARY]
GO
CREATE INDEX [ByCarrierPatient] ON [dbo].[Ar]([BRANCH_NUMBER],
[BILL_TYPE], [CARRIER_NUMBER], [PATIENT_NUMBER], [INVOICE_NUMBER], [ArType])
WITH FILLFACTOR = 90 ON [PRIMARY]
GO
CREATE INDEX [ByCompCarrier] ON [dbo].[Ar]([CARRIER_NUMBER], [BILL_TYPE],
[PATIENT_NUMBER], [INVOICE_NUMBER], [ArType]) WITH FILLFACTOR = 90 ON
[PRIMARY]
GO
CREATE INDEX [ByInvoice] ON [dbo].[Ar]([BRANCH_NUMBER], [INVOICE_NUMBER],
[ArType]) WITH FILLFACTOR = 90 ON [PRIMARY]
GO
CREATE INDEX [ByPatientCarrier] ON [dbo].[Ar]([BRANCH_NUMBER],
[PATIENT_NUMBER], [CARRIER_NUMBER], [INVOICE_NUMBER], [ArType]) WITH
FILLFACTOR = 90 ON [PRIMARY]
GO
CREATE INDEX [ByPatientInvoice] ON [dbo].[Ar]([BRANCH_NUMBER],
[PATIENT_NUMBER], [BILL_TYPE], [INVOICE_NUMBER], [ArType]) WITH FILLFACTOR
= 90 ON [PRIMARY]
GO
CREATE INDEX [CarrierIndex] ON [dbo].[Ar]([BRANCH_NUMBER],
[CARRIER_NUMBER], [ArType]) WITH FILLFACTOR = 90 ON [PRIMARY]
GO
CREATE INDEX [PatientIndex] ON [dbo].[Ar]([BRANCH_NUMBER],
[PATIENT_NUMBER], [ArType]) WITH FILLFACTOR = 90 ON [PRIMARY]
GO
CREATE INDEX [ByCompPatient] ON [dbo].[Ar]([PATIENT_NUMBER], [BILL_TYPE],
[CARRIER_NUMBER], [INVOICE_NUMBER], [ArType], [CAPITATED]) WITH FILLFACTOR
= 90 ON [PRIMARY]
GO
CREATE INDEX [Byitembal] ON [dbo].[Ar]([ITEM_BALANCE], [BILL_TYPE],
[CARRIER_NUMBER], [PATIENT_NUMBER], [INVOICE_NUMBER]) WITH FILLFACTOR = 90
ON [PRIMARY]
GO
CREATE INDEX [ByIVRx] ON [dbo].[Ar]([IvRxNumber], [IvMixingNumber],
[RP_OR_S], [PRODUCT_NUMBER], [ArType]) WITH FILLFACTOR = 90 ON [PRIMARY]
GO
CREATE INDEX [idx_ar_ordr_num] ON [dbo].[Ar]([ORDER_NUMBER]) WITH
FILLFACTOR = 90 ON [PRIMARY]
GO
CREATE INDEX [IX_ByDoctor] ON [dbo].[Ar]([DOCTOR_NUMBER]) WITH FILLFACTOR
= 90 ON [PRIMARY]
GO
CREATE INDEX [idxMSCClmXferAr1] ON [dbo].[Ar]([BRANCH_NUMBER],
[BILL_TYPE], [INVOICE_NUMBER]) WITH FILLFACTOR = 90 ON [PRIMARY]
GO
CREATE INDEX [idxMSCClmXferAr2] ON [dbo].[Ar]([BRANCH_NUMBER],
[BILL_TYPE], [INVOICE_NUMBER], [ITEM_BALANCE]) WITH FILLFACTOR = 90 ON
[PRIMARY]
GO
And the query
Select
a.BRANCH_NUMBER, a.INVOICE_NUMBER,a.Bill_Type, a.Carrier_Number,
a.ORDER_Number, a.product_number, a.Service_Date, a.RP_OR_S,
a.quantity_delivered, a.billed_amount, a.item_Balance,
a.Patient_number,
Balance=Round(a.Finance_Charge,2)+Round(a.Item_Balance,2),
a.Xo_Account_Numbers,a._20_Percent_Invoice,
a.XO_SOURCE_ACCT_NUMBER, a.TherapyException,
a.Allowable, a.amount,a.Finance_Charge, a.Procedure_Code,
a.TherapyException, a.numberofdays,a.Capitated,
NumberOfDaysAdmin = case
when a.NumberOfDaysAdmin > 0
then a.NumberOfDaysAdmin
when NumberOfDays > 0
then numberofdays
else 1
end
from
Ar a with (nolock)
where
( a.Item_Balance+isnull(a.Finance_Charge,0)) > 0
and (
a.Invoice_Number not in
(select Invoice_Number from tmpcash
where branch_number=a.Branch_number and bill_type=a.bill_type and
carrier_number=a.carrier_number and
patient_number=a.patient_number)
)
and (a.Invoice_Number not in
(select X_Invoice_Number from ARTransfer
where branch_number=a.Branch_number and
patient_number=a.patient_number
and x_bill_type=a.bill_type and
X_carrier_number=a.carrier_number ))
and a.Invoice_Number like '5987382%'
Order by a.Bill_Type,a.Patient_Number,a.Invoice_Number
"Wayne Snyder" <wayne.nospam.snyder@.mariner-usa.com> wrote in message
news:#n0GTKXdEHA.228@.TK2MSFTNGP11.phx.gbl...
> Also, Please post your DDL and DML to get more accurate responses.
> --
> 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
> "Navin Vishnu" <navinittiyamparampadh@.comcast.net> wrote in message
> news:%23QSDz$RdEHA.4092@.TK2MSFTNGP10.phx.gbl...
> > Hi,
> >
> > We have a table with approx 3.5 million rows. Queries against it are
slow.
> > One particular query which does a LIKE search on an invoice number takes
> 10
> > seconds to return 5 rows. There is a datatype conversion issue in the
LIKE
> > search but still the queries agains this are slow. If i split index and
> data
> > into seperate files would it help. How can i split the data in a table
to
> > seperate files?
> >
> > Thanks
> >
> > Navin
> >
> >
>|||The table has 4 million rows
Select
a.BRANCH_NUMBER, a.INVOICE_NUMBER,a.Bill_Type, a.Carrier_Number,
a.ORDER_Number, a.product_number, a.Service_Date, a.RP_OR_S,
a.quantity_delivered, a.billed_amount, a.item_Balance,
a.Patient_number,
Balance=Round(a.Finance_Charge,2)+Round(a.Item_Balance,2),
a.Xo_Account_Numbers,a._20_Percent_Invoice,
a.XO_SOURCE_ACCT_NUMBER, a.TherapyException,
a.Allowable, a.amount,a.Finance_Charge, a.Procedure_Code,
a.TherapyException, a.numberofdays,a.Capitated,
NumberOfDaysAdmin = case
when a.NumberOfDaysAdmin > 0
then a.NumberOfDaysAdmin
when NumberOfDays > 0
then numberofdays
else 1
end
from
Ar a with (nolock)
where
( a.Item_Balance+isnull(a.Finance_Charge,0)) > 0
and (
a.Invoice_Number not in
(select Invoice_Number from tmpcash
where branch_number=a.Branch_number and bill_type=a.bill_type and
carrier_number=a.carrier_number and
patient_number=a.patient_number)
)
and (a.Invoice_Number not in
(select X_Invoice_Number from ARTransfer
where branch_number=a.Branch_number and
patient_number=a.patient_number
and x_bill_type=a.bill_type and
X_carrier_number=a.carrier_number ))
and a.Invoice_Number like '5987382%'
Order by a.Bill_Type,a.Patient_Number,a.Invoice_Number
CREATE TABLE [dbo].[Ar] (
[BRANCH_NUMBER] [int] NOT NULL ,
[PATIENT_NUMBER] [int] NOT NULL ,
[BILL_TYPE] [char] (2) NOT NULL ,
[CARRIER_NUMBER] [int] NOT NULL ,
[INVOICE_NUMBER] [float] NOT NULL ,
[ORDER_NUMBER] [int] NULL ,
[MB_PREFIX] [int] NULL ,
[ORDER_TERMS] [int] NULL ,
[XO_ACCOUNT_NUMBERS] [char] (25) NULL ,
[XO_SOURCE_ACCT_NUMBER] [int] NULL ,
[DELIVERY_DATE] [smalldatetime] NULL ,
[SERVICE_DATE] [smalldatetime] NULL ,
[RP_OR_S] [char] (1) NULL ,
[N_OR_U] [char] (1) NULL ,
[PRODUCT_NUMBER] [char] (20) NULL ,
[QUANTITY_DELIVERED] [numeric](12, 2) NULL ,
[ITEM_PRICE] [money] NULL ,
[PROCEDURE_CODE] [char] (6) NULL ,
[ALLOWABLE] [money] NULL ,
[AMOUNT] [money] NULL ,
[ITEM_BALANCE] [money] NULL ,
[_20_PERCENT_INVOICE] [char] (1) NULL ,
[INVOICE_COST] [money] NULL ,
[TAX_CODE] [char] (5) NULL ,
[ITEM_TAX] [money] NULL ,
[SALESPERSON_NUMBER] [int] NULL ,
[REFERRAL_SOURCE_NUMBER] [numeric](12, 2) NULL ,
[BILLED_AMOUNT] [money] NULL ,
[DATE_RESUBMITTED] [smalldatetime] NULL ,
[DATE_RECORD_CREATED] [smalldatetime] NULL ,
[INVOICE_DATE] [smalldatetime] NULL ,
[TO_DATE] [smalldatetime] NULL ,
[CLAIM_STATUS] [char] (20) NULL ,
[CAPPED_RENTAL_MONTH] [smallint] NULL ,
[ENTERAL_TYPE] [char] (2) NULL ,
[MODIFIER_1] [char] (2) NULL ,
[MODIFIER_2] [char] (2) NULL ,
[SPECIAL_MODIFIER_1] [char] (2) NULL ,
[SPECIAL_MODIFIER_2] [char] (2) NULL ,
[PLACE_OF_SERVICE_CODE_E] [char] (2) NULL ,
[PLACE_OF_SERVICE_CODE_P] [char] (2) NULL ,
[SERVICE_TYPE] [char] (2) NULL ,
[MEDIA_TYPE] [char] (1) NULL ,
[ASSIGNED] [char] (1) NULL ,
[PA_NUMBER] [char] (20) NULL ,
[PA_REQUIRED] [char] (1) NULL ,
[HOW_MANY_REFILLS] [int] NULL ,
[REFILLS_ALLOWED] [int] NULL ,
[DOCTOR_NUMBER] [int] NULL ,
[ORIG_CO_INSURANCE] [money] NULL ,
[ORIG_PRIMARY_PAID] [money] NULL ,
[ORIG_DEDUCTIBLE] [money] NULL ,
[DATE_AR_INIT_ENTERED] [smalldatetime] NULL ,
[AR_INIT_USER] [char] (30) NULL ,
[SERVICE_TERMINATE_DATE] [smalldatetime] NULL ,
[PRIMARY_PAID_DATE] [smalldatetime] NULL ,
[SERVICE] [char] (6) NULL ,
[REVENUE_CODE] [char] (3) NULL ,
[COUNTY_CODE] [char] (4) NULL ,
[BILL_CODE] [numeric](12, 2) NULL ,
[MEDICAID_RESUB_CODE] [char] (15) NULL ,
[ORIGINAL_RE_NO] [char] (30) NULL ,
[COB] [char] (3) NULL ,
[EMEVS] [char] (20) NULL ,
[PRODUCT_DESCRIPTION] [char] (30) NULL ,
[BILL_UNITS] [numeric](12, 2) NULL ,
[ORIG_ALLOWABLE] [money] NULL ,
[ORIGINAL_RE_NO_2] [char] (15) NULL ,
[RECORD_STATUS] [char] (1) NULL ,
[TherapyCode] [int] NULL ,
[ArType] [char] (1) NULL ,
[IvRxNumber] [int] NULL ,
[IvMixingNumber] [int] NULL ,
[EMPLOYEE_NUMBER] [numeric](12, 2) NULL ,
[PREBILLED] [char] (1) NULL ,
[FINANCE_CHARGE] [smallmoney] NULL ,
[LAST_FIN_CHRG_DATE] [smalldatetime] NULL ,
[CLAIM_MSG_1] [char] (6) NULL ,
[CLAIM_MSG_2] [char] (6) NULL ,
[CLAIM_MSG_3] [char] (6) NULL ,
[CLAIM_MSG_4] [char] (6) NULL ,
[CLAIM_MSG_5] [char] (6) NULL ,
[NumberOfDays] [int] NULL ,
[BillAmount_Modified] [char] (1) NULL ,
[Their_PO_Number] [char] (20) NULL ,
[TherapyException] [char] (1) NULL ,
[SERIAL_NUMBERS] [text] NULL ,
[BILLING_TYPE] [char] (1) NULL ,
[CONTRACT_CODE] [char] (10) NULL ,
[IV_BU_Qty] [float] NULL ,
[CAPITATED] [char] (1) NULL ,
[IsMayo] [varchar] (1) NULL ,
[DaysRangeRental] [varchar] (1) NULL ,
[StartRentalDay] [int] NULL ,
[EndRentalDay] [int] NULL ,
[InvBatchID] [char] (20) NULL ,
[Capitated_Bill] [char] (1) NULL ,
[IsCoPay] [char] (1) NULL ,
[ITEM_PRICE_Old] [money] NULL ,
[ALLOWABLE_Old] [money] NULL ,
[BILLED_AMOUNT_Old] [money] NULL ,
[AMOUNT_Old] [money] NULL ,
[ORIG_PRIMARY_PAID_Old] [money] NULL ,
[ORIG_CO_INSURANCE_Old] [money] NULL ,
[ORIG_DEDUCTIBLE_Old] [money] NULL ,
[Cost_Center_Code] [char] (5) NULL ,
[Special_Price] [char] (1) NULL ,
[Ar_At_Allowed] [char] (1) NULL ,
[DeliveryNumber] [int] NULL ,
[NumberOfDaysAdmin] [int] NULL ,
[UserName] [char] (20) NULL ,
[ORDER_NUMBER_SUFFIX] [int] NULL ,
[AR_Note] [char] (40) NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
ALTER TABLE [dbo].[Ar] WITH NOCHECK ADD
CONSTRAINT [PK__Ar__5654B625] PRIMARY KEY CLUSTERED
(
[BRANCH_NUMBER],
[PATIENT_NUMBER],
[BILL_TYPE],
[CARRIER_NUMBER],
[INVOICE_NUMBER]
) WITH FILLFACTOR = 90 ON [PRIMARY]
GO
CREATE INDEX [BillTypeIndex] ON [dbo].[Ar]([BRANCH_NUMBER], [BILL_TYPE],
[ArType]) WITH FILLFACTOR = 90 ON [PRIMARY]
GO
CREATE INDEX [ByBillTypePatient] ON [dbo].[Ar]([BRANCH_NUMBER],
[BILL_TYPE], [PATIENT_NUMBER], [ArType]) WITH FILLFACTOR = 90 ON [PRIMARY]
GO
CREATE INDEX [ByCarrierInvoice] ON [dbo].[Ar]([BRANCH_NUMBER],
[CARRIER_NUMBER], [BILL_TYPE], [INVOICE_NUMBER], [ArType]) WITH FILLFACTOR
= 90 ON [PRIMARY]
GO
CREATE INDEX [ByCarrierPatient] ON [dbo].[Ar]([BRANCH_NUMBER],
[BILL_TYPE], [CARRIER_NUMBER], [PATIENT_NUMBER], [INVOICE_NUMBER], [ArType])
WITH FILLFACTOR = 90 ON [PRIMARY]
GO
CREATE INDEX [ByCompCarrier] ON [dbo].[Ar]([CARRIER_NUMBER], [BILL_TYPE],
[PATIENT_NUMBER], [INVOICE_NUMBER], [ArType]) WITH FILLFACTOR = 90 ON
[PRIMARY]
GO
CREATE INDEX [ByInvoice] ON [dbo].[Ar]([BRANCH_NUMBER], [INVOICE_NUMBER],
[ArType]) WITH FILLFACTOR = 90 ON [PRIMARY]
GO
CREATE INDEX [ByPatientCarrier] ON [dbo].[Ar]([BRANCH_NUMBER],
[PATIENT_NUMBER], [CARRIER_NUMBER], [INVOICE_NUMBER], [ArType]) WITH
FILLFACTOR = 90 ON [PRIMARY]
GO
CREATE INDEX [ByPatientInvoice] ON [dbo].[Ar]([BRANCH_NUMBER],
[PATIENT_NUMBER], [BILL_TYPE], [INVOICE_NUMBER], [ArType]) WITH FILLFACTOR
= 90 ON [PRIMARY]
GO
CREATE INDEX [CarrierIndex] ON [dbo].[Ar]([BRANCH_NUMBER],
[CARRIER_NUMBER], [ArType]) WITH FILLFACTOR = 90 ON [PRIMARY]
GO
CREATE INDEX [PatientIndex] ON [dbo].[Ar]([BRANCH_NUMBER],
[PATIENT_NUMBER], [ArType]) WITH FILLFACTOR = 90 ON [PRIMARY]
GO
CREATE INDEX [ByCompPatient] ON [dbo].[Ar]([PATIENT_NUMBER], [BILL_TYPE],
[CARRIER_NUMBER], [INVOICE_NUMBER], [ArType], [CAPITATED]) WITH FILLFACTOR
= 90 ON [PRIMARY]
GO
CREATE INDEX [Byitembal] ON [dbo].[Ar]([ITEM_BALANCE], [BILL_TYPE],
[CARRIER_NUMBER], [PATIENT_NUMBER], [INVOICE_NUMBER]) WITH FILLFACTOR = 90
ON [PRIMARY]
GO
CREATE INDEX [ByIVRx] ON [dbo].[Ar]([IvRxNumber], [IvMixingNumber],
[RP_OR_S], [PRODUCT_NUMBER], [ArType]) WITH FILLFACTOR = 90 ON [PRIMARY]
GO
CREATE INDEX [idx_ar_ordr_num] ON [dbo].[Ar]([ORDER_NUMBER]) WITH
FILLFACTOR = 90 ON [PRIMARY]
GO
CREATE INDEX [IX_ByDoctor] ON [dbo].[Ar]([DOCTOR_NUMBER]) WITH FILLFACTOR
= 90 ON [PRIMARY]
GO
CREATE INDEX [idxMSCClmXferAr1] ON [dbo].[Ar]([BRANCH_NUMBER],
[BILL_TYPE], [INVOICE_NUMBER]) WITH FILLFACTOR = 90 ON [PRIMARY]
GO
CREATE INDEX [idxMSCClmXferAr2] ON [dbo].[Ar]([BRANCH_NUMBER],
[BILL_TYPE], [INVOICE_NUMBER], [ITEM_BALANCE]) WITH FILLFACTOR = 90 ON
[PRIMARY]
GO
"Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
news:#Lz3jESdEHA.412@.TK2MSFTNGP10.phx.gbl...
> Hi.,
> Before splitting the Non clusteredd Index and data into seperate files try
> to do these:-
> 1. Check your select statement is using an index using Execution plan
> 2. Execute DBCC SHOWCONTIG and see the table is fragmented. If fragmented
> remove the fragmentation by DBCC DBREINDEX
> 3. Run the Update statistics on the tables in FROM Clause
>
> Thanks
> Hari
> MCDBA
>
>
> "Navin Vishnu" <navinittiyamparampadh@.comcast.net> wrote in message
> news:#QSDz$RdEHA.4092@.TK2MSFTNGP10.phx.gbl...
> > Hi,
> >
> > We have a table with approx 3.5 million rows. Queries against it are
slow.
> > One particular query which does a LIKE search on an invoice number takes
> 10
> > seconds to return 5 rows. There is a datatype conversion issue in the
LIKE
> > search but still the queries agains this are slow. If i split index and
> data
> > into seperate files would it help. How can i split the data in a table
to
> > seperate files?
> >
> > Thanks
> >
> > Navin
> >
> >
>|||On Thu, 29 Jul 2004 21:39:31 -0400, Navin Vishnu wrote:
>The table is appromimatly 4 million rows.
>Here's the table structure
(snip)
Hi Navin,
Try adding an index with (or modifying an existing index) with
invoice_number as the FIRST column. Or change the column order on one of
the existing indexes that already cover invoice_number.
Note that changing an existing index might hurt performance of other
queries whereas adding an index will hurt insert, update and delete
performance.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)