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_NUMB
ER],
[BILL_TYPE], [PATIENT_NUMBER], [ArType]) WITH FILLFACTOR = 90 O
N [PRIMARY]
GO
CREATE INDEX [ByCarrierInvoice] ON [dbo].[Ar]([BRANCH_NUMBE
R],
[CARRIER_NUMBER], [BILL_TYPE], [INVOICE_NUMBER], [ArType]) W
ITH FILLFACTOR
= 90 ON [PRIMARY]
GO
CREATE INDEX [ByCarrierPatient] ON [dbo].[Ar]([BRANCH_NUMBE
R],
[BILL_TYPE], [CARRIER_NUMBER], [PATIENT_NUMBER], [INVOICE_NU
MBER], [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],
1;INVOICE_NUMBER],
[ArType]) WITH FILLFACTOR = 90 ON [PRIMARY]
GO
CREATE INDEX [ByPatientCarrier] ON [dbo].[Ar]([BRANCH_NUMBE
R],
[PATIENT_NUMBER], [CARRIER_NUMBER], [INVOICE_NUMBER], [ArTyp
e]) WITH
FILLFACTOR = 90 ON [PRIMARY]
GO
CREATE INDEX [ByPatientInvoice] ON [dbo].[Ar]([BRANCH_NUMBE
R],
[PATIENT_NUMBER], [BILL_TYPE], [INVOICE_NUMBER], [ArType]) W
ITH 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]) W
ITH FILLFACTOR
= 90 ON [PRIMARY]
GO
CREATE INDEX [Byitembal] ON [dbo].[Ar]([ITEM_BALANCE], [
;BILL_TYPE],
[CARRIER_NUMBER], [PATIENT_NUMBER], [INVOICE_NUMBER]) WITH FILL
FACTOR = 90
ON [PRIMARY]
GO
CREATE INDEX [ByIVRx] ON [dbo].[Ar]([IvRxNumber], [IvMi
xingNumber],
[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]) W
ITH FILLFACTOR
= 90 ON [PRIMARY]
GO
CREATE INDEX [idxMSCClmXferAr1] ON [dbo].[Ar]([BRANCH_NUMBE
R],
[BILL_TYPE], [INVOICE_NUMBER]) WITH FILLFACTOR = 90 ON [PRIMARY
]
GO
CREATE INDEX [idxMSCClmXferAr2] ON [dbo].[Ar]([BRANCH_NUMBE
R],
[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...
slow.[vbcol=seagreen]
> 10
LIKE[vbcol=seagreen]
> data
to[vbcol=seagreen]
>|||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_NUMB
ER],
[BILL_TYPE], [PATIENT_NUMBER], [ArType]) WITH FILLFACTOR = 90 O
N [PRIMARY]
GO
CREATE INDEX [ByCarrierInvoice] ON [dbo].[Ar]([BRANCH_NUMBE
R],
[CARRIER_NUMBER], [BILL_TYPE], [INVOICE_NUMBER], [ArType]) W
ITH FILLFACTOR
= 90 ON [PRIMARY]
GO
CREATE INDEX [ByCarrierPatient] ON [dbo].[Ar]([BRANCH_NUMBE
R],
[BILL_TYPE], [CARRIER_NUMBER], [PATIENT_NUMBER], [INVOICE_NU
MBER], [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],
1;INVOICE_NUMBER],
[ArType]) WITH FILLFACTOR = 90 ON [PRIMARY]
GO
CREATE INDEX [ByPatientCarrier] ON [dbo].[Ar]([BRANCH_NUMBE
R],
[PATIENT_NUMBER], [CARRIER_NUMBER], [INVOICE_NUMBER], [ArTyp
e]) WITH
FILLFACTOR = 90 ON [PRIMARY]
GO
CREATE INDEX [ByPatientInvoice] ON [dbo].[Ar]([BRANCH_NUMBE
R],
[PATIENT_NUMBER], [BILL_TYPE], [INVOICE_NUMBER], [ArType]) W
ITH 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]) W
ITH FILLFACTOR
= 90 ON [PRIMARY]
GO
CREATE INDEX [Byitembal] ON [dbo].[Ar]([ITEM_BALANCE], [
;BILL_TYPE],
[CARRIER_NUMBER], [PATIENT_NUMBER], [INVOICE_NUMBER]) WITH FILL
FACTOR = 90
ON [PRIMARY]
GO
CREATE INDEX [ByIVRx] ON [dbo].[Ar]([IvRxNumber], [IvMi
xingNumber],
[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]) W
ITH FILLFACTOR
= 90 ON [PRIMARY]
GO
CREATE INDEX [idxMSCClmXferAr1] ON [dbo].[Ar]([BRANCH_NUMBE
R],
[BILL_TYPE], [INVOICE_NUMBER]) WITH FILLFACTOR = 90 ON [PRIMARY
]
GO
CREATE INDEX [idxMSCClmXferAr2] ON [dbo].[Ar]([BRANCH_NUMBE
R],
[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...
slow.[vbcol=seagreen]
> 10
LIKE[vbcol=seagreen]
> data
to[vbcol=seagreen]
>|||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)

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)

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
Navin
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
>
|||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_Bal ance,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...[vbcol=seagreen]
> 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...
slow.[vbcol=seagreen]
> 10
LIKE[vbcol=seagreen]
> data
to
>
|||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_Bal ance,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...[vbcol=seagreen]
> 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...
slow.[vbcol=seagreen]
> 10
LIKE[vbcol=seagreen]
> data
to
>
|||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)

Performance effect of using maxOccurs="unbounded"

Hello,

In our application we have a few elements in our schema where we have not set limits on the number of occurences. For these particular elements, the maxOccurs attribute is set to "unbounded".

Does this have any performance effect on any XML indexes that we may add later to improve performance? Does it slow anything down internally in SQL Server when performing any DML operations on our stored XML?

It does not make a difference to XML indexing whether maxOccurs is set to unbounded or a fixed value (> 1, say 6). A separate entry is created in the XML index for each occurrence of the element. So index lookup and other other optimizations will occur the same way. There should be no performance difference between the two choices.

If maxOccurs is 1, then you should set it to 1.

Thank you,

Shankar Pal, Program Manager, SQL Server

Performance DEV vs PROD - Same Equipment??

DEV and PROD same exact box, PROD has 4GB and DEV 1GB. A particular
query takes over an hour in PROD and about 20 minutes in DEV? As far as
I can tell their SQLServer settings are the same, I do have limited access.
Read something about a /3GB switch, we are using SQLServer 2000 and the
semi-dba indicated that we did not have that setup but didn't think it
needed to be. The semi-dba type is mainly Sybase and can't seem to find
any spare cycles to look at my problem. I am a developer and of course
past writing some SQL am far from expert on the DB stuff.
I have googled and found various information about databases and
indexing, which BTW, did find that our FillFactor was set incorrectly
as this system is reporting, updated once a month from a feed from the
main system. So reindexed everything with a fillFactor of 100%. It was
previously set at 75%? Didn't see any improvement in DEV so don't expect
anyhing great in PROD.
Anything is helpful at this point. I will be running to HalfPrice
books tomorrow to see what I can find that can help me.
JimDOn Sat, 14 Apr 2007 23:29:12 GMT, Jim Douglas
<james.douglas@.genesis-software.com> wrote:
>DEV and PROD same exact box, PROD has 4GB and DEV 1GB. A particular
>query takes over an hour in PROD and about 20 minutes in DEV? As far as
>I can tell their SQLServer settings are the same, I do have limited access.
Not to overlook the obvious, are you certain the *data* is the same in
Dev?
Try running profiler on both to see what resources are getting used.
J.|||Hum., 20 minutes in DEV: with this kind of result, you shouldn't be
surprised to see the PROD to take much longer if its size is four times as
big.
--
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: sylvain aei ca (fill the blanks, no spam please)
"Jim Douglas" <james.douglas@.genesis-software.com> wrote in message
news:ctdUh.1139$xP.1027@.trnddc04...
> DEV and PROD same exact box, PROD has 4GB and DEV 1GB. A particular query
> takes over an hour in PROD and about 20 minutes in DEV? As far as I can
> tell their SQLServer settings are the same, I do have limited access.
> Read something about a /3GB switch, we are using SQLServer 2000 and the
> semi-dba indicated that we did not have that setup but didn't think it
> needed to be. The semi-dba type is mainly Sybase and can't seem to find
> any spare cycles to look at my problem. I am a developer and of course
> past writing some SQL am far from expert on the DB stuff.
> I have googled and found various information about databases and indexing,
> which BTW, did find that our FillFactor was set incorrectly
> as this system is reporting, updated once a month from a feed from the
> main system. So reindexed everything with a fillFactor of 100%. It was
> previously set at 75%? Didn't see any improvement in DEV so don't expect
> anyhing great in PROD.
> Anything is helpful at this point. I will be running to HalfPrice
> books tomorrow to see what I can find that can help me.
> JimD
>|||"Jim Douglas" <james.douglas@.genesis-software.com> wrote in message
news:ctdUh.1139$xP.1027@.trnddc04...
> DEV and PROD same exact box, PROD has 4GB and DEV 1GB. A particular query
> takes over an hour in PROD and about 20 minutes in DEV? As far as I can
> tell their SQLServer settings are the same, I do have limited access.
What is the query?
And what is the schema for the tables involved?
A 20 minute query is a pretty long query in most cases (but not all.)
> Read something about a /3GB switch, we are using SQLServer 2000 and the
> semi-dba indicated that we did not have that setup but didn't think it
> needed to be. The semi-dba type is mainly Sybase and can't seem to find
> any spare cycles to look at my problem. I am a developer and of course
> past writing some SQL am far from expert on the DB stuff.
> I have googled and found various information about databases and indexing,
> which BTW, did find that our FillFactor was set incorrectly
> as this system is reporting, updated once a month from a feed from the
> main system. So reindexed everything with a fillFactor of 100%. It was
> previously set at 75%? Didn't see any improvement in DEV so don't expect
> anyhing great in PROD.
What made you decide to change this? Is this a datawarehouse or an OLTP.
In some cases 100% fill factor can make overall performance far worse.
(generally if yo're only selecting, it's great, you'll pack as much data
into the nodes of the b-tree as possible, but inserts will cost a lot more
overheard.)
> Anything is helpful at this point. I will be running to HalfPrice
> books tomorrow to see what I can find that can help me.
You running SQL 2000 or 2005?
> JimD
>
Greg Moore
SQL Server DBA Consulting Remote and Onsite available!
Email: sql (at) greenms.com http://www.greenms.com/sqlserver.html|||Hi Jim
"Jim Douglas" wrote:
> DEV and PROD same exact box, PROD has 4GB and DEV 1GB. A particular
> query takes over an hour in PROD and about 20 minutes in DEV? As far as
> I can tell their SQLServer settings are the same, I do have limited access.
> Read something about a /3GB switch, we are using SQLServer 2000 and the
> semi-dba indicated that we did not have that setup but didn't think it
> needed to be. The semi-dba type is mainly Sybase and can't seem to find
> any spare cycles to look at my problem. I am a developer and of course
> past writing some SQL am far from expert on the DB stuff.
> I have googled and found various information about databases and
> indexing, which BTW, did find that our FillFactor was set incorrectly
> as this system is reporting, updated once a month from a feed from the
> main system. So reindexed everything with a fillFactor of 100%. It was
> previously set at 75%? Didn't see any improvement in DEV so don't expect
> anyhing great in PROD.
> Anything is helpful at this point. I will be running to HalfPrice
> books tomorrow to see what I can find that can help me.
> JimD
To add my 2c...
The usage of the two systems is likely to be different if these are not
processes that set the database into single user mode!! You may have blocking
on live which if you are the only person testing on your own database will
not happen on DEV. Rather than testing on DEV you may want to consider a
separate test environment which you can run simulated loads on. You should
also look at the query plans on the two different environments and also check
index fragmentations and the validity of the statistics.
John

perform calculation for only desired rows

hi all, i wasnt quite sure where to look to answer my particular problem so i am posting up this thread in the hopes that someone can point me in the right direction. in my report, i am showing sales figures for an area. i added a table to display sales from this year, sales from last year, and then the comparable percentage('this' divided by 'last' and then minus one). to account for some ppl who didnt have sales last year, i was able to use an IIF expression to return "N/A" in the textbox. my problem is i want the compared percentage to show for the area total, but not including ppl who were an 'N/A'. i am assuming that some sort of expression will be needed for the area total row, i.e. i want the area total to sum up this year and last year and then get the percentage, but i need to filter out the individuals who didnt have data for last year.

as an example

nsty nsly percentage

total: 15 7 X

A: 5 4 25%

B: 5 3 66%

C: 5 0 N/A

the percentage for total, X, should be (10/7)-1, with p0 as the format; right now it is summing rows A, B, and C.. how can i exclude row C from the calculation?

am i on the right track by researching filters and expressions, or is this a matter for the query

thanks in advance for any help

Hi

An expression will be able to handle this fine.
Use the IIF function to only include nsty if nsly is greater than zero.
For your total % sum use something like this:

= (Sum(IIF(Fields!nsly.value > 0,Fields!nsty.value,0))/Sum(Fields!nsly.Value)) -1

Hope this helps

Cheers
Mark
|||

thanks for the response; i think i still need to expand on the expression a little bit but at least i know how to go about it now.

Perform an experssion in desing mode

Hi all,

SSIS, I would like to perform/validate an experssion in design mode. In particular, i would like to see the value of the varibles before running the whole package. I tried several time in Command Windows or Immedite Window unsuccessfully. Do those windows can only be used in running mode? Or how can I do that in other way?

Thanks

Where are you performing the expression? In the Control Flow or Data Flow.

This BOL entry describes how to evaluate an expression at design time in the Control Flow: http://msdn2.microsoft.com/en-us/library/ms141698.aspx

Expressions in the data flow are validated automatically at design time and any errors will be raised - but they can not be evaluated at design time.

Debug windows are only available when the package is running in the debugger.

Donald Farmer

|||

That's mean I must create varibles in Control flow or date flow even though i just want to see the result of 1+1? Can't it like in VB or C# IDE?

|||

I'm still not clear where you are using the expression, so it's difficult to help in detail. You can evaluate Property Expressions, but not expressions in the data flow.

Donald

Percentage Help

I need to write a page that figures out the percentage of students passing an assessment item where students are from a particular school, grade, gender, and are not medically exempt.

I did a Count Statement on all test taker, and another Count Statement for the number passing the item who were not exempt. Both of the statements are shown through seperate datalist controls. I then want to get the percentage by dividing the number passed by the number taken and mulitplying that by 100.

Is there a better technique or can I do this in a sql statement somehow?

Show your table or tables here. Which version of SQL Server are you using? 2000 or 2005?