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 Help

I have a 40 gig database (SQL 2005 Standard) and need to run several hundred
queries against it every night. These queries are for reporting purposes and
need to run within certain time constraints. I have added indexes to the
tables, but the queries could potentially include filter criteria for any
number of 100 or so columns. At this point, i am at a loss of how to optimize
a database of this size properly to faciliate these requirements. Any
thoughts?
Thanks,
Matt
--
Message posted via http://www.sqlmonster.comOne easy solution could be to use Database Engine Tuning Advisor (DTA).
Create a trace using Profiler or (better) a server trace to collect all these
queries and use this trace file as input to DTA. Then follow the index and
statistics recommendations from DTA.
A better solution would be to use a tuning methodology like the one
described by Itzik Ben-Gan on his book Inside SQL Server 2005: T-SQL Querying
(Chapter 3, Tuning Methodology).
Hope this helps,
Ben Nevarez
"lotek via SQLMonster.com" wrote:
> I have a 40 gig database (SQL 2005 Standard) and need to run several hundred
> queries against it every night. These queries are for reporting purposes and
> need to run within certain time constraints. I have added indexes to the
> tables, but the queries could potentially include filter criteria for any
> number of 100 or so columns. At this point, i am at a loss of how to optimize
> a database of this size properly to faciliate these requirements. Any
> thoughts?
> Thanks,
> Matt
> --
> Message posted via http://www.sqlmonster.com
>|||Some rather high-level and general suggestions:
1. Use SQL Trace to record these queries, and use the recorded queries as
your base workload for performance analysis.
2. Run the workload through the database engine tuning advisor and analyze
the index recommendations. Note that the recommendations from the database
tuning advisor may not always be spot on, but you can take them as input to
your analysis.
3. Concentrate on the top N most expensive queries and focus your tuning
efforts them first.
Linchi
"lotek via SQLMonster.com" wrote:
> I have a 40 gig database (SQL 2005 Standard) and need to run several hundred
> queries against it every night. These queries are for reporting purposes and
> need to run within certain time constraints. I have added indexes to the
> tables, but the queries could potentially include filter criteria for any
> number of 100 or so columns. At this point, i am at a loss of how to optimize
> a database of this size properly to faciliate these requirements. Any
> thoughts?
> Thanks,
> Matt
> --
> Message posted via http://www.sqlmonster.com
>|||Make sure that the queries are executed at a time when the server is not
busy doing anything else, when it is experiencing the least amount of
activity. You may also want to check what else runs on the server outside
of mssql and terminate as many unnecessary running processes as possible.
--
Sincerely,
John K
Knowledgy Consulting
www.knowledgy.org
Atlanta's Business Intelligence and Data Warehouse Experts
"Linchi Shea" <LinchiShea@.discussions.microsoft.com> wrote in message
news:83BE6CA5-2EA6-4177-8559-07675C44F8DE@.microsoft.com...
> Some rather high-level and general suggestions:
> 1. Use SQL Trace to record these queries, and use the recorded queries as
> your base workload for performance analysis.
> 2. Run the workload through the database engine tuning advisor and analyze
> the index recommendations. Note that the recommendations from the database
> tuning advisor may not always be spot on, but you can take them as input
> to
> your analysis.
> 3. Concentrate on the top N most expensive queries and focus your tuning
> efforts them first.
> Linchi
> "lotek via SQLMonster.com" wrote:
>> I have a 40 gig database (SQL 2005 Standard) and need to run several
>> hundred
>> queries against it every night. These queries are for reporting purposes
>> and
>> need to run within certain time constraints. I have added indexes to the
>> tables, but the queries could potentially include filter criteria for any
>> number of 100 or so columns. At this point, i am at a loss of how to
>> optimize
>> a database of this size properly to faciliate these requirements. Any
>> thoughts?
>> Thanks,
>> Matt
>> --
>> Message posted via http://www.sqlmonster.com
>>|||also, see if you can partition any of your data and tables and run your
queries in parallel
--
Sincerely,
John K
Knowledgy Consulting
www.knowledgy.org
Atlanta's Business Intelligence and Data Warehouse Experts
"lotek via SQLMonster.com" <u16539@.uwe> wrote in message
news:7fc7a9d0d08f9@.uwe...
>I have a 40 gig database (SQL 2005 Standard) and need to run several
>hundred
> queries against it every night. These queries are for reporting purposes
> and
> need to run within certain time constraints. I have added indexes to the
> tables, but the queries could potentially include filter criteria for any
> number of 100 or so columns. At this point, i am at a loss of how to
> optimize
> a database of this size properly to faciliate these requirements. Any
> thoughts?
> Thanks,
> Matt
> --
> Message posted via http://www.sqlmonster.com
>|||1) Get professional assistance
2) Buy better hardware (especially RAM, more CPUs and FAST I/O)
3) Investigate partitioning
4) If you use lots of char-type fields, investigate Full Text Indexing
5) If your queries access large portions of the data (10-20% plus) indexing
(full or regular) won't help because the optimizer will choose table scans
(unless you have covering indexes).
6) Check for locking. Consider using NOLOCK hints on the report queries.
Kevin G. Boles
Indicium Resources, Inc.
SQL Server MVP
kgboles a earthlink dt net
"lotek via SQLMonster.com" <u16539@.uwe> wrote in message
news:7fc7a9d0d08f9@.uwe...
>I have a 40 gig database (SQL 2005 Standard) and need to run several
>hundred
> queries against it every night. These queries are for reporting purposes
> and
> need to run within certain time constraints. I have added indexes to the
> tables, but the queries could potentially include filter criteria for any
> number of 100 or so columns. At this point, i am at a loss of how to
> optimize
> a database of this size properly to faciliate these requirements. Any
> thoughts?
> Thanks,
> Matt
> --
> Message posted via http://www.sqlmonster.com
>sql

Performance Help

I have a 40 gig database (SQL 2005 Standard) and need to run several hundred
queries against it every night. These queries are for reporting purposes and
need to run within certain time constraints. I have added indexes to the
tables, but the queries could potentially include filter criteria for any
number of 100 or so columns. At this point, i am at a loss of how to optimize
a database of this size properly to faciliate these requirements. Any
thoughts?
Thanks,
Matt
Message posted via http://www.droptable.com
One easy solution could be to use Database Engine Tuning Advisor (DTA).
Create a trace using Profiler or (better) a server trace to collect all these
queries and use this trace file as input to DTA. Then follow the index and
statistics recommendations from DTA.
A better solution would be to use a tuning methodology like the one
described by Itzik Ben-Gan on his book Inside SQL Server 2005: T-SQL Querying
(Chapter 3, Tuning Methodology).
Hope this helps,
Ben Nevarez
"lotek via droptable.com" wrote:

> I have a 40 gig database (SQL 2005 Standard) and need to run several hundred
> queries against it every night. These queries are for reporting purposes and
> need to run within certain time constraints. I have added indexes to the
> tables, but the queries could potentially include filter criteria for any
> number of 100 or so columns. At this point, i am at a loss of how to optimize
> a database of this size properly to faciliate these requirements. Any
> thoughts?
> Thanks,
> Matt
> --
> Message posted via http://www.droptable.com
>
|||Some rather high-level and general suggestions:
1. Use SQL Trace to record these queries, and use the recorded queries as
your base workload for performance analysis.
2. Run the workload through the database engine tuning advisor and analyze
the index recommendations. Note that the recommendations from the database
tuning advisor may not always be spot on, but you can take them as input to
your analysis.
3. Concentrate on the top N most expensive queries and focus your tuning
efforts them first.
Linchi
"lotek via droptable.com" wrote:

> I have a 40 gig database (SQL 2005 Standard) and need to run several hundred
> queries against it every night. These queries are for reporting purposes and
> need to run within certain time constraints. I have added indexes to the
> tables, but the queries could potentially include filter criteria for any
> number of 100 or so columns. At this point, i am at a loss of how to optimize
> a database of this size properly to faciliate these requirements. Any
> thoughts?
> Thanks,
> Matt
> --
> Message posted via http://www.droptable.com
>
|||Make sure that the queries are executed at a time when the server is not
busy doing anything else, when it is experiencing the least amount of
activity. You may also want to check what else runs on the server outside
of mssql and terminate as many unnecessary running processes as possible.
Sincerely,
John K
Knowledgy Consulting
www.knowledgy.org
Atlanta's Business Intelligence and Data Warehouse Experts
"Linchi Shea" <LinchiShea@.discussions.microsoft.com> wrote in message
news:83BE6CA5-2EA6-4177-8559-07675C44F8DE@.microsoft.com...[vbcol=seagreen]
> Some rather high-level and general suggestions:
> 1. Use SQL Trace to record these queries, and use the recorded queries as
> your base workload for performance analysis.
> 2. Run the workload through the database engine tuning advisor and analyze
> the index recommendations. Note that the recommendations from the database
> tuning advisor may not always be spot on, but you can take them as input
> to
> your analysis.
> 3. Concentrate on the top N most expensive queries and focus your tuning
> efforts them first.
> Linchi
> "lotek via droptable.com" wrote:
|||also, see if you can partition any of your data and tables and run your
queries in parallel
Sincerely,
John K
Knowledgy Consulting
www.knowledgy.org
Atlanta's Business Intelligence and Data Warehouse Experts
"lotek via droptable.com" <u16539@.uwe> wrote in message
news:7fc7a9d0d08f9@.uwe...
>I have a 40 gig database (SQL 2005 Standard) and need to run several
>hundred
> queries against it every night. These queries are for reporting purposes
> and
> need to run within certain time constraints. I have added indexes to the
> tables, but the queries could potentially include filter criteria for any
> number of 100 or so columns. At this point, i am at a loss of how to
> optimize
> a database of this size properly to faciliate these requirements. Any
> thoughts?
> Thanks,
> Matt
> --
> Message posted via http://www.droptable.com
>
|||1) Get professional assistance
2) Buy better hardware (especially RAM, more CPUs and FAST I/O)
3) Investigate partitioning
4) If you use lots of char-type fields, investigate Full Text Indexing
5) If your queries access large portions of the data (10-20% plus) indexing
(full or regular) won't help because the optimizer will choose table scans
(unless you have covering indexes).
6) Check for locking. Consider using NOLOCK hints on the report queries.
Kevin G. Boles
Indicium Resources, Inc.
SQL Server MVP
kgboles a earthlink dt net
"lotek via droptable.com" <u16539@.uwe> wrote in message
news:7fc7a9d0d08f9@.uwe...
>I have a 40 gig database (SQL 2005 Standard) and need to run several
>hundred
> queries against it every night. These queries are for reporting purposes
> and
> need to run within certain time constraints. I have added indexes to the
> tables, but the queries could potentially include filter criteria for any
> number of 100 or so columns. At this point, i am at a loss of how to
> optimize
> a database of this size properly to faciliate these requirements. Any
> thoughts?
> Thanks,
> Matt
> --
> Message posted via http://www.droptable.com
>

performance differnce between 3027 and 3033

Hi,

I have found major differnces in performance between the two versions - specially with queries that should be answerd from cache if i execute multiple times...

Test Query - after the first execute it tooks

1 second with 9.0.3027 and 12 seconds at 9.0.3033.

Is this a bug in the last ctp - does this change back in the released SP2? or is the something bad in my models for the latest version

we used two equal machines - processed the db on 9.0.3033 and restored the backup on 9.0.3027

query looks like

select

{[4WochenSet]* [Zeit].[Tag].[Tag].Members} on axis(0),

Descendants([Immobilien].[Geographie].[Bundesland].&[Wien]&[AUT], [Immobilien].[Geographie].[PLZ],SELF) on axis(1)

from

Angebotsanalyse

where ([Measures].[Kaufpreis je Wohnflaeche MW], [Plattform].[Plattform].&[156],[Immobilien].[Immobilientyp].&[5])

LG, HANNES

Are there any calculations used by the query? How is the set [4WochenSet] defined? If you capture the performance counters under MSAS 2005:MDX during the query, do you see any significant difference in performance counter values between the two builds? For example, do you see a much larger increase in the value of the [Total calculation covers] performance counter in build 3033? If there is calculation involved in the query, such IIF, try connection string property "Cache Policy=9" to see if there is any difference in performance.

Jeffrey

|||

There are some calc, the measure [Kaufpreis je Wohnflaeche MW] is defined as

AVG(existing({[Immobilien].[Immobilie].[Immobilie].Members}),[Measures].[Kaufpreis je Wohnflaeche])

and the set is defined as

{Tail(except(

exists({[Zeit].[Zeit nach Wochen].[Woche].Members},[Zeit].[Abgeschlossen].&[True]),

exists({[Zeit].[Zeit nach Wochen].[Woche].Members},[Zeit].[Abgeschlossen].&[False])

),4)}

by this definition there are no if calculations, but the base measure [Measures].[Kaufpreis je Wohnflaeche] is defined as lastnonempty.

At the moment i test only using management studio - later i will test it with your sugestions.

Is this further optimized in the released version of SP2? so we do not need the setting?

LG, HANNES

|||

It's hard to tell what caused the performance degredation. But since there is no IIF and other calculation conditions, "Cache Policy=9" shouldn't make any difference.

performance differences between queries...

I am updating a db with data from a file, in this data we have new info, info that has been updated and info that is to be removed from the db.
Now I was wondering which approach results in better performance/shorter executin time:

1. first update excisting values, then insert new ones, and last delete cancelled data

or

2. delete cancelled data and data that will be updated, then insert new and updated info

I get all this data from a file, in that file all rows are similar and there is one column that defines if the data is new, updated or to be deleted (thus all the updates also include the information for the enty that has not been altered).

// PatiPati, I think there are too many unknown variables to properly answer the question. For instance, how many columns are you updating compared to how many columns are not being updated? If you're updating one small column, and there are ten large columns, say Char(255), that are not being updated, then I believe an update would be faster than a "delete and insert all new rows". However, you would have to have the index to support finding the rows that need updating.

Personally, I always choose the way that makes the most sense logically. I would delete cancelled data, then update existing data and then insert the new rows.

Roby2222|||Thanks for you answer Roby2222, I hope this additional info will clarify things.

The amount of columns that are being updated differs from time to time, sometimes there is just one column that needs updating and sometimes almost all columns have to be updated...

Most columns are about the same size 'cause their max size is predefined in the system where it originates from.

Basically I was just wondering if it is worth doing 3 steps (delete+update+insert) when you get the same result with 2 steps (delet+insert)?

Pati|||Pati,

If you're doing a bulk insert, then the two step process will usually be quicker. If you're inserting row by row, then it's probably a toss up. If you really want to know the exact answer for your particular situation, I would run a test and benchmark both methods. You can use one scripts with all of the necessary statements, and then not how long it took to run in query analyzer.

Hope this helps,
Roby2222|||Thanks for your answers!

I think I'll go for the 2 step solution, 'cause it will probably result in simpler code (and therefore easier for someone else to read later if neccessary).

Perhaps I'll also test the performance manually as you suggested sometime later on when I'll have some time to spare! :)

Pati

Performance difference using IN or OR

Hello!
1. Is there a performance difference using the IN keyword or the OR keyword?
2. Does SQL Server internally convert one of the queries into another, e.g.
a query using IN into a query using OR?
3. When should I use OR and when IN (I know about using IN with subselects)?
For example look at those two simple queries:
USE AdventureWorks;
GO
SELECT FirstName, LastName, e.Title
FROM HumanResources.Employee AS e
JOIN Person.Contact AS c
ON e.ContactID = c.ContactID
WHERE e.Title = 'Design Engineer'
OR e.Title = 'Tool Designer'
OR e.Title = 'Marketing Assistant';
GO
USE AdventureWorks;
GO
SELECT FirstName, LastName, e.Title
FROM HumanResources.Employee AS e
JOIN Person.Contact AS c
ON e.ContactID = c.ContactID
WHERE e.Title IN ('Design Engineer', 'Tool Designer', 'Marketing Assistant')
;
GO
Thanks
RobAFAIK
> 1. Is there a performance difference using the IN keyword or the OR
> keyword?
NO
> 2. Does SQL Server internally convert one of the queries into another,
> e.g.
> a query using IN into a query using OR?
YES (Not in the case of subqueries)
> 3. When should I use OR and when IN (I know about using IN with
> subselects)?
Personally, I use IN when there are 2 or more items in the list.
Regards
Roji. P. Thomas
http://toponewithties.blogspot.com
"RobRoma" <RobRoma@.discussions.microsoft.com> wrote in message
news:6ADFDCAF-E3D8-4F93-9823-FF19C982C1A7@.microsoft.com...
> Hello!
> 1. Is there a performance difference using the IN keyword or the OR
> keyword?
> 2. Does SQL Server internally convert one of the queries into another,
> e.g.
> a query using IN into a query using OR?
> 3. When should I use OR and when IN (I know about using IN with
> subselects)?
> For example look at those two simple queries:
> USE AdventureWorks;
> GO
> SELECT FirstName, LastName, e.Title
> FROM HumanResources.Employee AS e
> JOIN Person.Contact AS c
> ON e.ContactID = c.ContactID
> WHERE e.Title = 'Design Engineer'
> OR e.Title = 'Tool Designer'
> OR e.Title = 'Marketing Assistant';
> GO
> USE AdventureWorks;
> GO
> SELECT FirstName, LastName, e.Title
> FROM HumanResources.Employee AS e
> JOIN Person.Contact AS c
> ON e.ContactID = c.ContactID
> WHERE e.Title IN ('Design Engineer', 'Tool Designer', 'Marketing
> Assistant');
> GO
> Thanks
> Rob
>
>|||You can run both the queries in one batch with the "show execution plan "
option ON and check the query cost relative to the bacth.. Both will have 50
%
Moreover you can check the PREDICATE in the execution plan for the query
using the IN clause. You will notice that it has substituted it with OR
conditions.
So using IN is not a problem, and its definitely more readable than the OR
clause.
--
-Omnibuzz (The SQL GC)
http://omnibuzz-sql.blogspot.com/|||When thinking in sets, I prefer using EXISTS or filtering the results in the
JOIN clause.
With these two the comparison can be multi-dimensional, rather than
uni-dimensional as is the case with the IN and OR (i.e. only one condition i
s
checked).
Of course possible NULL values must be taken into account.
ML
http://milambda.blogspot.com/|||OR will likely give you better performance provided that you have
usable indexes on your table. IN is not a searchable argument (SARG)
which means that the query optimizer cannot use IN to analyze possible
indexes to use when selecting data. If your where clause used only the
IN expression, your rows would be returned using a table scan. However,
if you used OR instead, and you had an index on the columns in your OR
expression, the query optimizer would evaluate that expression against
your available indexes and possibly choose to use an index scan or
s.
I hope that all made sense. In any case, I would use OR in queries that
will be run often, but if you are just writing ad hoc reports and your
environment can afford it, IN should be fine to use if you want to save
yourself some typing.
HTH
Jason|||Jason,
Where did you come up with this unusual analysis of the optimizer?
Have you ever tested it? It is easily disproved. The optimizer has
no trouble using an index to find values from an IN.
Roy Harvey
Beacon Falls, CT
On 28 Jun 2006 10:49:57 -0700, "Jason" <jnorsworthy@.etrade.com> wrote:

>OR will likely give you better performance provided that you have
>usable indexes on your table. IN is not a searchable argument (SARG)
>which means that the query optimizer cannot use IN to analyze possible
>indexes to use when selecting data. If your where clause used only the
>IN expression, your rows would be returned using a table scan. However,
>if you used OR instead, and you had an index on the columns in your OR
>expression, the query optimizer would evaluate that expression against
>your available indexes and possibly choose to use an index scan or
>s.
>I hope that all made sense. In any case, I would use OR in queries that
>will be run often, but if you are just writing ad hoc reports and your
>environment can afford it, IN should be fine to use if you want to save
>yourself some typing.
>HTH
>Jason|||DOH!
Thanks for catching this one Roy, I had read this (or misread) a while
back in "Inside Microsoft SQL Server 2000" which is a GREAT book - if
you read it correctly :P. My analysis regarding SARGs is correct, but
due to line spacing and my haste in reading I mistakenly read/believed
that IN wasn't a SARG, however it is NOT IN that isn't a SARG - which
actually makes a lot more sense.
Sorry for any confusion!
Jason
Roy Harvey wrote:
> Jason,
> Where did you come up with this unusual analysis of the optimizer?
> Have you ever tested it? It is easily disproved. The optimizer has
> no trouble using an index to find values from an IN.
> Roy Harvey
> Beacon Falls, CT
>

Performance DashBoard

Hi All,

I am using the Performance Dashboard to gather more information for my queries.

I have noticed that when I select the "On Duration" option under the Expensive Queries section I get a list of 20 queries ordered by the highest CPU time. I have been able to play around with the .rdl file (in particular the query_stat.rdl) and made it so that it displays more. The max seems to be 100 for display though.

I was wondering whether anyone knew of how to make it so that there is no max, and how to order the queries based on LEAST CPU time.

Thanks.

You might look at dm_exec_query_stat DMV in this case to get the result.|||

How did you change the number of queries returned from 20 to 100? I would assume you modified the .rdl file or the underlying stored procedure in SQL Server. You could do the same thing to change the sort order or the column on which you sort the result set. If you're just interested in the information, you could take Satya SKJ's advice and write your own query directly against the Dynamic Management Views.

Paul A. Mestemaker II

Program Manager

Microsoft SQL Server Manageability

http://blogs.msdn.com/sqlrem/

Performance considerations UDF vs Joins

Hi all,
New to SQL, but getting into it, converting Access queries to SQL.
I have a query in access which includes the following
SELECT
...
Case when A.Z is not null then C1.Y else C2.Y END AS MNO
...
FROM (A INNER JOIN
(B LEFT JOIN C C1 ON B.Z = C1.PK)
ON A.PK = B.PK)
LEFT JOIN C C2 ON A.Z = C2.PK;
This works fine in SQL Server, but I've also written a UDF which is
passed A.Z and returns MNO through direct reference to table C
(SELECT @.MNO = Y FROM C WHERE PK = '' + @.ValIn + ''
I;m only working on a small DB, so performance is not an issue, but
I'm curious as to which should have better performance considerations
so I know which way to take things. The UDF certainly makes things
tidyier, so I will stick with it unless there's a reason not to.
Thanks,
MattMatt
> (SELECT @.MNO = Y FROM C WHERE PK = '' + @.ValIn + ''
Does ther UDF accept a parameter?
(Untested)
SELECT .......
CASE
WHEN EXISTS (
SELECT * FROM C WHERE PK = Value)
THEN C1.Y
ELSE C2.Y
END AS MNO
...
FROM (A INNER JOIN
(B LEFT JOIN C C1 ON B.Z = C1.PK)
ON A.PK = B.PK)
LEFT JOIN C C2 ON A.Z = C2.PK;
PS. Have a look at execution plan of yours query?
You can make it as a stored procedure which will accept a parameter so I
gave an idea .
"Matt Bolton" <m3it@.technologist.com> wrote in message
news:8kdrv0tr5l9dlk16vl0dl97hsj959u1rfv@.
4ax.com...
> Hi all,
> New to SQL, but getting into it, converting Access queries to SQL.
> I have a query in access which includes the following
> SELECT
> ...
> Case when A.Z is not null then C1.Y else C2.Y END AS MNO
> ...
> FROM (A INNER JOIN
> (B LEFT JOIN C C1 ON B.Z = C1.PK)
> ON A.PK = B.PK)
> LEFT JOIN C C2 ON A.Z = C2.PK;
> This works fine in SQL Server, but I've also written a UDF which is
> passed A.Z and returns MNO through direct reference to table C
> (SELECT @.MNO = Y FROM C WHERE PK = '' + @.ValIn + ''
> I;m only working on a small DB, so performance is not an issue, but
> I'm curious as to which should have better performance considerations
> so I know which way to take things. The UDF certainly makes things
> tidyier, so I will stick with it unless there's a reason not to.
> Thanks,
> Matt|||Yes, accepts A.Z and B.Z (If A.Z is null, then uses B.Z to get
required value).
I'll try looking at execution plan - forgot about that option. I
gather there are no general rules with this sort of thing, since
execution plan really answers the question definitively.
Thanks,
Matt
On Mon, 31 Jan 2005 07:23:20 +0200, "Uri Dimant" <urid@.iscar.co.il>
wrote:

>Matt
>Does ther UDF accept a parameter?
>(Untested)
>SELECT .......
>CASE
> WHEN EXISTS (
> SELECT * FROM C WHERE PK = Value)
> THEN C1.Y
> ELSE C2.Y
> END AS MNO
> ...
> FROM (A INNER JOIN
> (B LEFT JOIN C C1 ON B.Z = C1.PK)
> ON A.PK = B.PK)
> LEFT JOIN C C2 ON A.Z = C2.PK;
>
>PS. Have a look at execution plan of yours query?
>You can make it as a stored procedure which will accept a parameter so I
>gave an idea .
>
>"Matt Bolton" <m3it@.technologist.com> wrote in message
> news:8kdrv0tr5l9dlk16vl0dl97hsj959u1rfv@.
4ax.com...
>|||On Mon, 31 Jan 2005 15:56:44 +1100, Matt Bolton wrote:

>I have a query in access which includes the following
>SELECT
> ...
> Case when A.Z is not null then C1.Y else C2.Y END AS MNO
> ...
>FROM (A INNER JOIN
> (B LEFT JOIN C C1 ON B.Z = C1.PK)
> ON A.PK = B.PK)
> LEFT JOIN C C2 ON A.Z = C2.PK;
>This works fine in SQL Server, but I've also written a UDF which is
>passed A.Z and returns MNO through direct reference to table C
>(SELECT @.MNO = Y FROM C WHERE PK = '' + @.ValIn + ''
>I;m only working on a small DB, so performance is not an issue, but
>I'm curious as to which should have better performance considerations
>so I know which way to take things. The UDF certainly makes things
>tidyier, so I will stick with it unless there's a reason not to.
Hi Matt,
I'll come to your question shortly. First another remark.
Are you sure you made no errors when copying the query in your message? It
seems to me that if A.Z is not null, you'd want to select C2.Y, not C1.Y,
as C2.Y is joined on A.Z and C1.Y on B.Z. The code you posted will display
the description for B.Z from the lookup table if A.Z has a value and will
display NULL is A.Z has no value (as C2.Y will be NULL in that case). In
the rest of the message, I'll assume that you meant it to be the other way
around.
WRT the question: performance will probably be better if you stick to the
current version. The UDF you use is scalar (you don't post the complete
code, but this is what I conclude from what you do post). This means that
it will be called once for each row in the result set. And for each call,
a seperate read in the lookup table has to be performed. For the query
above, the execution plan MIGHT be the same - but it need not. The
optimizer will consider the various indexes available, check some
statistics and then create an execution plan with the best expected
exectution speed. Of course, as Uri indicated, actually testing the
different versions is the best way to know for certasin which will be the
fastest.
You mention "tidyier". This is of course very much based on personal
preferences, so take the following with a grain of salt - but I think that
you could make your current query tidyier without using a UDF, simply by
exchanging the order in which the tables appear in the FROM clause:
SELECT ....
CASE WHEN A.Z IS NOT NULL THEN C1.Y ELSE C2.Y END AS MNO,
-- or: COALESCE (C1,Y, C2.Y) -- if this can be used depends on your data
...
FROM A
INNER JOIN B
ON B.PK = A.PK
LEFT JOIN C AS C1
ON C1.PK = A.Z
LEFT JOIN C AS C2
ON C2.PK = B.Z
If you only need the C table for the lookup in the select and don't use
any value from C in the rest of the query, you could also consider the
following alternative (but do test it for performance impact!):
SELECT ....
(SELECT C.Y
FROM C
WHERE C.PK = COALESCE (A.Z, B.Z)) AS MNO,
...
FROM A
INNER JOIN B
ON B.PK = A.PK
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||Hugo,
Yes, you're correct about C1 and C2 being swapped. Thanks for the
suggestions - the COALESCE (C1,Y, C2.Y) is simplest and works.
I agree that my join clause is a bit untidy - it's the output of a VB
module I use to make writing complex queries easier - but it needs a
bit of tidying itself.
I'd got the impression from some of the reading I've done here that I
should try to do less calculation in queries and as much as possible
in SP/UDF, and the main thrust of my question was to confirm that -
which you (and others) haven't - so either way I now have a clearer
understanding of where my project's headed.
Thanks,
Matt
On Mon, 31 Jan 2005 09:23:29 +0100, Hugo Kornelis
<hugo@.pe_NO_rFact.in_SPAM_fo> wrote:

>On Mon, 31 Jan 2005 15:56:44 +1100, Matt Bolton wrote:
>
>Hi Matt,
>I'll come to your question shortly. First another remark.
>Are you sure you made no errors when copying the query in your message? It
>seems to me that if A.Z is not null, you'd want to select C2.Y, not C1.Y,
>as C2.Y is joined on A.Z and C1.Y on B.Z. The code you posted will display
>the description for B.Z from the lookup table if A.Z has a value and will
>display NULL is A.Z has no value (as C2.Y will be NULL in that case). In
>the rest of the message, I'll assume that you meant it to be the other way
>around.
>WRT the question: performance will probably be better if you stick to the
>current version. The UDF you use is scalar (you don't post the complete
>code, but this is what I conclude from what you do post). This means that
>it will be called once for each row in the result set. And for each call,
>a seperate read in the lookup table has to be performed. For the query
>above, the execution plan MIGHT be the same - but it need not. The
>optimizer will consider the various indexes available, check some
>statistics and then create an execution plan with the best expected
>exectution speed. Of course, as Uri indicated, actually testing the
>different versions is the best way to know for certasin which will be the
>fastest.
>You mention "tidyier". This is of course very much based on personal
>preferences, so take the following with a grain of salt - but I think that
>you could make your current query tidyier without using a UDF, simply by
>exchanging the order in which the tables appear in the FROM clause:
>SELECT ....
> CASE WHEN A.Z IS NOT NULL THEN C1.Y ELSE C2.Y END AS MNO,
>-- or: COALESCE (C1,Y, C2.Y) -- if this can be used depends on your data
> ....
>FROM A
>INNER JOIN B
> ON B.PK = A.PK
>LEFT JOIN C AS C1
> ON C1.PK = A.Z
>LEFT JOIN C AS C2
> ON C2.PK = B.Z
>If you only need the C table for the lookup in the select and don't use
>any value from C in the rest of the query, you could also consider the
>following alternative (but do test it for performance impact!):
>SELECT ....
> (SELECT C.Y
> FROM C
> WHERE C.PK = COALESCE (A.Z, B.Z)) AS MNO,
> ....
>FROM A
>INNER JOIN B
> ON B.PK = A.PK
>Best, Hugo|||On Tue, 01 Feb 2005 10:01:25 +1100, Matt Bolton wrote:
(snip)
>I'd got the impression from some of the reading I've done here that I
>should try to do less calculation in queries and as much as possible
>in SP/UDF,
(snip)
Hi Matt,
In SP: yes. Not because SP will perform better than queries, but for two
other reasons:
1. Security: don't give any user any rights to modify data in any table;
instead, give them only execute permission on stored procedures that will
perform the desired modifications in a controleed manner. Or take it a
step further: don't give any rights to view the data in the tables or
views at all; give them some more stored procedures to get the requested
data.
2. Indirect performance benefit: because you can control the quality of
the SQL in the procedures and fine-tune your indexes to the contents of
these procedures, you can gain performance. You'll never have that level
of control if you allow application programmers or even end users to
create their own ad-hoc queries.
In UDF: no. Especially scalar UDF's are a performance drag, because they
ahve to be executed once for each row. A scalar UDF that reads data from a
table will allways be outperformed by incorporating the logic into the
query. Of course, there may be other reasons that make the UDF the best
choice, but you do take the performance hit.
Table-valued UDF are not nearly as bad. In fact, a query using inline
table-valued UDFs will often result in the same execution plan as an
equivalent query that replaces the UDF by incorporating it's logic into
the query.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||Thanks again Hugo for your assistance.
Matt
On Tue, 01 Feb 2005 00:56:48 +0100, Hugo Kornelis
<hugo@.pe_NO_rFact.in_SPAM_fo> wrote:

>On Tue, 01 Feb 2005 10:01:25 +1100, Matt Bolton wrote:
>(snip)
>(snip)
>Hi Matt,
>In SP: yes. Not because SP will perform better than queries, but for two
>other reasons:
>1. Security: don't give any user any rights to modify data in any table;
>instead, give them only execute permission on stored procedures that will
>perform the desired modifications in a controleed manner. Or take it a
>step further: don't give any rights to view the data in the tables or
>views at all; give them some more stored procedures to get the requested
>data.
>2. Indirect performance benefit: because you can control the quality of
>the SQL in the procedures and fine-tune your indexes to the contents of
>these procedures, you can gain performance. You'll never have that level
>of control if you allow application programmers or even end users to
>create their own ad-hoc queries.
>
>In UDF: no. Especially scalar UDF's are a performance drag, because they
>ahve to be executed once for each row. A scalar UDF that reads data from a
>table will allways be outperformed by incorporating the logic into the
>query. Of course, there may be other reasons that make the UDF the best
>choice, but you do take the performance hit.
>Table-valued UDF are not nearly as bad. In fact, a query using inline
>table-valued UDFs will often result in the same execution plan as an
>equivalent query that replaces the UDF by incorporating it's logic into
>the query.
>Best, Hugosql