Hi all,
We have db that has been configured w/ about 7 different filegroups.
Still trying to fully understand filegroups and their impact if tables
and indices aren't properly separated. I believe I read that you can
create different files for ea. index, but they must belong to the same
filegroup. In this example below they look to be in separate filegroups.
Is this a bad way of setting up filegroups? Am I multiplying the amt of
threads (exponentially) with this type of set up, which is causing my
i/o bottleneck?
Thank You!
C
ROWCTTBLKEYFILEGRP
15517322TESTSHIFTX1_TESTSHIFTtest1
15422280TESTSHIFTPK_TESTSHIFTtest1
15422280TESTSHIFTX2_TESTSHIFTtest3
15422280TESTSHIFTX3_TESTSHIFTtest4
15422280TESTSHIFTX4_TESTSHIFTtest1
13319460SHIFTPK_SHIFTtest4
13228153SHIFTX1_SHIFTtest1
13228153SHIFTX2_SHIFTtest1
13228153SHIFTX3_SHIFTtest1
13228153SHIFTX4_SHIFTtest1
13228153SHIFTX5_SHIFTtest2
13228153SHIFTX6_SHIFTtest1
10829933TESTSHFTRCTESTSHFTRCtest3
10748087TESTSHFTRCX1_TESTSHFTRCtest2
10748087TESTSHFTRCX2_TESTSHFTRCtest1
9105520TEST_LOOKUPTEST_LOOKUPtest4
8583208TESTSHIFTSEGPK_TESTSHIFTSEGtest2
8502786TESTSHIFTSEGX1_TESTSHIFTSEGtest1
If all of these files (and hence filegroups) are on the same drive array it
won't much matter how many you have. If your array can not handle the I/O
load then adding more files will not make it any better. The biggest
advantage to splitting data and indexes into two (or more) filegroups is
only when the files in those filegroups live on separate physical drive
arrays.
Andrew J. Kelly SQL MVP
"Ooroster" <chris@.nyp.org> wrote in message
news:eFb6pvuDFHA.2876@.TK2MSFTNGP12.phx.gbl...
> Hi all,
> We have db that has been configured w/ about 7 different filegroups.
> Still trying to fully understand filegroups and their impact if tables and
> indices aren't properly separated. I believe I read that you can create
> different files for ea. index, but they must belong to the same filegroup.
> In this example below they look to be in separate filegroups. Is this a
> bad way of setting up filegroups? Am I multiplying the amt of threads
> (exponentially) with this type of set up, which is causing my i/o
> bottleneck?
> Thank You!
> C
> ROWCT TBL KEY FILEGRP
> 15517322 TESTSHIFT X1_TESTSHIFT test1
> 15422280 TESTSHIFT PK_TESTSHIFT test1
> 15422280 TESTSHIFT X2_TESTSHIFT test3
> 15422280 TESTSHIFT X3_TESTSHIFT test4
> 15422280 TESTSHIFT X4_TESTSHIFT test1
> 13319460 SHIFT PK_SHIFT test4
> 13228153 SHIFT X1_SHIFT test1
> 13228153 SHIFT X2_SHIFT test1
> 13228153 SHIFT X3_SHIFT test1
> 13228153 SHIFT X4_SHIFT test1
> 13228153 SHIFT X5_SHIFT test2
> 13228153 SHIFT X6_SHIFT test1
> 10829933 TESTSHFTRC TESTSHFTRC test3
> 10748087 TESTSHFTRC X1_TESTSHFTRC test2
> 10748087 TESTSHFTRC X2_TESTSHFTRC test1
> 9105520 TEST_LOOKUP TEST_LOOKUP test4
> 8583208 TESTSHIFTSEG PK_TESTSHIFTSEG test2
> 8502786 TESTSHIFTSEG X1_TESTSHIFTSEG test1
|||Ahhhh, ok. Could there ever be an instance where you group data and
indices in a way that hurts performance even though the files have been
separated onto their own raid sets?
Thanks much for the info Andrew! It's been very helpful.
|||Separating indexes and tables onto their own drive arrays should only
increase performance (or at worst case the same) over keeping them all
together on one array. That is as long as you don't remove drives from the
original array.
Andrew J. Kelly SQL MVP
"Ooroster" <chris@.nyp.org> wrote in message
news:u7UQ$ixDFHA.4032@.TK2MSFTNGP10.phx.gbl...
> Ahhhh, ok. Could there ever be an instance where you group data and
> indices in a way that hurts performance even though the files have been
> separated onto their own raid sets?
> Thanks much for the info Andrew! It's been very helpful.
|||One example where you can hurt yourself with RAID volume segregation is when
you simply make Bad decisions on RAID layout.
For example:
you start with a single RAID 5 Volume with 4 disks and on that volume, you
place everything. OS, Logs, Data, Indexes.
You attempt to segregate data by creating seperate RAID Volumes: You create
2 RAID 5 Volumes with 2 disks each: placing OS and logs on One volume and
you place Data and indexes on the other volume.
You went from having a single decent volume (enough disks for striping and
parity) and you shoot yourself in the foot by creating two volumes that cant
really benefit from striping.
this could be an example where you hurt performance....again, you really
need to understand RAID confiuration to make this stuff work right but
generally speaking, segregation via filegroups is not going to add over head
and reduce performance.
Greg Jackson
Portland, Oregon
sql
Showing posts with label hit. Show all posts
Showing posts with label hit. Show all posts
Friday, March 30, 2012
Performance Hit w/ Filegroups?
Hi all,
We have db that has been configured w/ about 7 different filegroups.
Still trying to fully understand filegroups and their impact if tables
and indices aren't properly separated. I believe I read that you can
create different files for ea. index, but they must belong to the same
filegroup. In this example below they look to be in separate filegroups.
Is this a bad way of setting up filegroups? Am I multiplying the amt of
threads (exponentially) with this type of set up, which is causing my
i/o bottleneck?
Thank You!
C
ROWCT TBL KEY FILEGRP
15517322 TESTSHIFT X1_TESTSHIFT test1
15422280 TESTSHIFT PK_TESTSHIFT test1
15422280 TESTSHIFT X2_TESTSHIFT test3
15422280 TESTSHIFT X3_TESTSHIFT test4
15422280 TESTSHIFT X4_TESTSHIFT test1
13319460 SHIFT PK_SHIFT test4
13228153 SHIFT X1_SHIFT test1
13228153 SHIFT X2_SHIFT test1
13228153 SHIFT X3_SHIFT test1
13228153 SHIFT X4_SHIFT test1
13228153 SHIFT X5_SHIFT test2
13228153 SHIFT X6_SHIFT test1
10829933 TESTSHFTRC TESTSHFTRC test3
10748087 TESTSHFTRC X1_TESTSHFTRC test2
10748087 TESTSHFTRC X2_TESTSHFTRC test1
9105520 TEST_LOOKUP TEST_LOOKUP test4
8583208 TESTSHIFTSEG PK_TESTSHIFTSEG test2
8502786 TESTSHIFTSEG X1_TESTSHIFTSEG test1If all of these files (and hence filegroups) are on the same drive array it
won't much matter how many you have. If your array can not handle the I/O
load then adding more files will not make it any better. The biggest
advantage to splitting data and indexes into two (or more) filegroups is
only when the files in those filegroups live on separate physical drive
arrays.
--
Andrew J. Kelly SQL MVP
"Ooroster" <chris@.nyp.org> wrote in message
news:eFb6pvuDFHA.2876@.TK2MSFTNGP12.phx.gbl...
> Hi all,
> We have db that has been configured w/ about 7 different filegroups.
> Still trying to fully understand filegroups and their impact if tables and
> indices aren't properly separated. I believe I read that you can create
> different files for ea. index, but they must belong to the same filegroup.
> In this example below they look to be in separate filegroups. Is this a
> bad way of setting up filegroups? Am I multiplying the amt of threads
> (exponentially) with this type of set up, which is causing my i/o
> bottleneck?
> Thank You!
> C
> ROWCT TBL KEY FILEGRP
> 15517322 TESTSHIFT X1_TESTSHIFT test1
> 15422280 TESTSHIFT PK_TESTSHIFT test1
> 15422280 TESTSHIFT X2_TESTSHIFT test3
> 15422280 TESTSHIFT X3_TESTSHIFT test4
> 15422280 TESTSHIFT X4_TESTSHIFT test1
> 13319460 SHIFT PK_SHIFT test4
> 13228153 SHIFT X1_SHIFT test1
> 13228153 SHIFT X2_SHIFT test1
> 13228153 SHIFT X3_SHIFT test1
> 13228153 SHIFT X4_SHIFT test1
> 13228153 SHIFT X5_SHIFT test2
> 13228153 SHIFT X6_SHIFT test1
> 10829933 TESTSHFTRC TESTSHFTRC test3
> 10748087 TESTSHFTRC X1_TESTSHFTRC test2
> 10748087 TESTSHFTRC X2_TESTSHFTRC test1
> 9105520 TEST_LOOKUP TEST_LOOKUP test4
> 8583208 TESTSHIFTSEG PK_TESTSHIFTSEG test2
> 8502786 TESTSHIFTSEG X1_TESTSHIFTSEG test1|||Ahhhh, ok. Could there ever be an instance where you group data and
indices in a way that hurts performance even though the files have been
separated onto their own raid sets?
Thanks much for the info Andrew! It's been very helpful.|||Separating indexes and tables onto their own drive arrays should only
increase performance (or at worst case the same) over keeping them all
together on one array. That is as long as you don't remove drives from the
original array.
--
Andrew J. Kelly SQL MVP
"Ooroster" <chris@.nyp.org> wrote in message
news:u7UQ$ixDFHA.4032@.TK2MSFTNGP10.phx.gbl...
> Ahhhh, ok. Could there ever be an instance where you group data and
> indices in a way that hurts performance even though the files have been
> separated onto their own raid sets?
> Thanks much for the info Andrew! It's been very helpful.|||One example where you can hurt yourself with RAID volume segregation is when
you simply make Bad decisions on RAID layout.
For example:
you start with a single RAID 5 Volume with 4 disks and on that volume, you
place everything. OS, Logs, Data, Indexes.
You attempt to segregate data by creating seperate RAID Volumes: You create
2 RAID 5 Volumes with 2 disks each: placing OS and logs on One volume and
you place Data and indexes on the other volume.
You went from having a single decent volume (enough disks for striping and
parity) and you shoot yourself in the foot by creating two volumes that cant
really benefit from striping.
this could be an example where you hurt performance....again, you really
need to understand RAID confiuration to make this stuff work right but
generally speaking, segregation via filegroups is not going to add over head
and reduce performance.
Greg Jackson
Portland, Oregon
We have db that has been configured w/ about 7 different filegroups.
Still trying to fully understand filegroups and their impact if tables
and indices aren't properly separated. I believe I read that you can
create different files for ea. index, but they must belong to the same
filegroup. In this example below they look to be in separate filegroups.
Is this a bad way of setting up filegroups? Am I multiplying the amt of
threads (exponentially) with this type of set up, which is causing my
i/o bottleneck?
Thank You!
C
ROWCT TBL KEY FILEGRP
15517322 TESTSHIFT X1_TESTSHIFT test1
15422280 TESTSHIFT PK_TESTSHIFT test1
15422280 TESTSHIFT X2_TESTSHIFT test3
15422280 TESTSHIFT X3_TESTSHIFT test4
15422280 TESTSHIFT X4_TESTSHIFT test1
13319460 SHIFT PK_SHIFT test4
13228153 SHIFT X1_SHIFT test1
13228153 SHIFT X2_SHIFT test1
13228153 SHIFT X3_SHIFT test1
13228153 SHIFT X4_SHIFT test1
13228153 SHIFT X5_SHIFT test2
13228153 SHIFT X6_SHIFT test1
10829933 TESTSHFTRC TESTSHFTRC test3
10748087 TESTSHFTRC X1_TESTSHFTRC test2
10748087 TESTSHFTRC X2_TESTSHFTRC test1
9105520 TEST_LOOKUP TEST_LOOKUP test4
8583208 TESTSHIFTSEG PK_TESTSHIFTSEG test2
8502786 TESTSHIFTSEG X1_TESTSHIFTSEG test1If all of these files (and hence filegroups) are on the same drive array it
won't much matter how many you have. If your array can not handle the I/O
load then adding more files will not make it any better. The biggest
advantage to splitting data and indexes into two (or more) filegroups is
only when the files in those filegroups live on separate physical drive
arrays.
--
Andrew J. Kelly SQL MVP
"Ooroster" <chris@.nyp.org> wrote in message
news:eFb6pvuDFHA.2876@.TK2MSFTNGP12.phx.gbl...
> Hi all,
> We have db that has been configured w/ about 7 different filegroups.
> Still trying to fully understand filegroups and their impact if tables and
> indices aren't properly separated. I believe I read that you can create
> different files for ea. index, but they must belong to the same filegroup.
> In this example below they look to be in separate filegroups. Is this a
> bad way of setting up filegroups? Am I multiplying the amt of threads
> (exponentially) with this type of set up, which is causing my i/o
> bottleneck?
> Thank You!
> C
> ROWCT TBL KEY FILEGRP
> 15517322 TESTSHIFT X1_TESTSHIFT test1
> 15422280 TESTSHIFT PK_TESTSHIFT test1
> 15422280 TESTSHIFT X2_TESTSHIFT test3
> 15422280 TESTSHIFT X3_TESTSHIFT test4
> 15422280 TESTSHIFT X4_TESTSHIFT test1
> 13319460 SHIFT PK_SHIFT test4
> 13228153 SHIFT X1_SHIFT test1
> 13228153 SHIFT X2_SHIFT test1
> 13228153 SHIFT X3_SHIFT test1
> 13228153 SHIFT X4_SHIFT test1
> 13228153 SHIFT X5_SHIFT test2
> 13228153 SHIFT X6_SHIFT test1
> 10829933 TESTSHFTRC TESTSHFTRC test3
> 10748087 TESTSHFTRC X1_TESTSHFTRC test2
> 10748087 TESTSHFTRC X2_TESTSHFTRC test1
> 9105520 TEST_LOOKUP TEST_LOOKUP test4
> 8583208 TESTSHIFTSEG PK_TESTSHIFTSEG test2
> 8502786 TESTSHIFTSEG X1_TESTSHIFTSEG test1|||Ahhhh, ok. Could there ever be an instance where you group data and
indices in a way that hurts performance even though the files have been
separated onto their own raid sets?
Thanks much for the info Andrew! It's been very helpful.|||Separating indexes and tables onto their own drive arrays should only
increase performance (or at worst case the same) over keeping them all
together on one array. That is as long as you don't remove drives from the
original array.
--
Andrew J. Kelly SQL MVP
"Ooroster" <chris@.nyp.org> wrote in message
news:u7UQ$ixDFHA.4032@.TK2MSFTNGP10.phx.gbl...
> Ahhhh, ok. Could there ever be an instance where you group data and
> indices in a way that hurts performance even though the files have been
> separated onto their own raid sets?
> Thanks much for the info Andrew! It's been very helpful.|||One example where you can hurt yourself with RAID volume segregation is when
you simply make Bad decisions on RAID layout.
For example:
you start with a single RAID 5 Volume with 4 disks and on that volume, you
place everything. OS, Logs, Data, Indexes.
You attempt to segregate data by creating seperate RAID Volumes: You create
2 RAID 5 Volumes with 2 disks each: placing OS and logs on One volume and
you place Data and indexes on the other volume.
You went from having a single decent volume (enough disks for striping and
parity) and you shoot yourself in the foot by creating two volumes that cant
really benefit from striping.
this could be an example where you hurt performance....again, you really
need to understand RAID confiuration to make this stuff work right but
generally speaking, segregation via filegroups is not going to add over head
and reduce performance.
Greg Jackson
Portland, Oregon
Performance Hit w/ Filegroups?
Hi all,
We have db that has been configured w/ about 7 different filegroups.
Still trying to fully understand filegroups and their impact if tables
and indices aren't properly separated. I believe I read that you can
create different files for ea. index, but they must belong to the same
filegroup. In this example below they look to be in separate filegroups.
Is this a bad way of setting up filegroups? Am I multiplying the amt of
threads (exponentially) with this type of set up, which is causing my
i/o bottleneck?
Thank You!
C
ROWCT TBL KEY FILEGRP
15517322 TESTSHIFT X1_TESTSHIFT test1
15422280 TESTSHIFT PK_TESTSHIFT test1
15422280 TESTSHIFT X2_TESTSHIFT test3
15422280 TESTSHIFT X3_TESTSHIFT test4
15422280 TESTSHIFT X4_TESTSHIFT test1
13319460 SHIFT PK_SHIFT test4
13228153 SHIFT X1_SHIFT test1
13228153 SHIFT X2_SHIFT test1
13228153 SHIFT X3_SHIFT test1
13228153 SHIFT X4_SHIFT test1
13228153 SHIFT X5_SHIFT test2
13228153 SHIFT X6_SHIFT test1
10829933 TESTSHFTRC TESTSHFTRC test3
10748087 TESTSHFTRC X1_TESTSHFTRC test2
10748087 TESTSHFTRC X2_TESTSHFTRC test1
9105520 TEST_LOOKUP TEST_LOOKUP test4
8583208 TESTSHIFTSEG PK_TESTSHIFTSEG te
st2
8502786 TESTSHIFTSEG X1_TESTSHIFTSEG te
st1If all of these files (and hence filegroups) are on the same drive array it
won't much matter how many you have. If your array can not handle the I/O
load then adding more files will not make it any better. The biggest
advantage to splitting data and indexes into two (or more) filegroups is
only when the files in those filegroups live on separate physical drive
arrays.
Andrew J. Kelly SQL MVP
"Ooroster" <chris@.nyp.org> wrote in message
news:eFb6pvuDFHA.2876@.TK2MSFTNGP12.phx.gbl...
> Hi all,
> We have db that has been configured w/ about 7 different filegroups.
> Still trying to fully understand filegroups and their impact if tables and
> indices aren't properly separated. I believe I read that you can create
> different files for ea. index, but they must belong to the same filegroup.
> In this example below they look to be in separate filegroups. Is this a
> bad way of setting up filegroups? Am I multiplying the amt of threads
> (exponentially) with this type of set up, which is causing my i/o
> bottleneck?
> Thank You!
> C
> ROWCT TBL KEY FILEGRP
> 15517322 TESTSHIFT X1_TESTSHIFT test1
> 15422280 TESTSHIFT PK_TESTSHIFT test1
> 15422280 TESTSHIFT X2_TESTSHIFT test3
> 15422280 TESTSHIFT X3_TESTSHIFT test4
> 15422280 TESTSHIFT X4_TESTSHIFT test1
> 13319460 SHIFT PK_SHIFT test4
> 13228153 SHIFT X1_SHIFT test1
> 13228153 SHIFT X2_SHIFT test1
> 13228153 SHIFT X3_SHIFT test1
> 13228153 SHIFT X4_SHIFT test1
> 13228153 SHIFT X5_SHIFT test2
> 13228153 SHIFT X6_SHIFT test1
> 10829933 TESTSHFTRC TESTSHFTRC test3
> 10748087 TESTSHFTRC X1_TESTSHFTRC test2
> 10748087 TESTSHFTRC X2_TESTSHFTRC test1
> 9105520 TEST_LOOKUP TEST_LOOKUP test4
> 8583208 TESTSHIFTSEG PK_TESTSHIFTSEG test2
> 8502786 TESTSHIFTSEG X1_TESTSHIFTSEG test1|||Ahhhh, ok. Could there ever be an instance where you group data and
indices in a way that hurts performance even though the files have been
separated onto their own raid sets?
Thanks much for the info Andrew! It's been very helpful.|||Separating indexes and tables onto their own drive arrays should only
increase performance (or at worst case the same) over keeping them all
together on one array. That is as long as you don't remove drives from the
original array.
Andrew J. Kelly SQL MVP
"Ooroster" <chris@.nyp.org> wrote in message
news:u7UQ$ixDFHA.4032@.TK2MSFTNGP10.phx.gbl...
> Ahhhh, ok. Could there ever be an instance where you group data and
> indices in a way that hurts performance even though the files have been
> separated onto their own raid sets?
> Thanks much for the info Andrew! It's been very helpful.|||One example where you can hurt yourself with RAID volume segregation is when
you simply make Bad decisions on RAID layout.
For example:
you start with a single RAID 5 Volume with 4 disks and on that volume, you
place everything. OS, Logs, Data, Indexes.
You attempt to segregate data by creating seperate RAID Volumes: You create
2 RAID 5 Volumes with 2 disks each: placing OS and logs on One volume and
you place Data and indexes on the other volume.
You went from having a single decent volume (enough disks for striping and
parity) and you shoot yourself in the foot by creating two volumes that cant
really benefit from striping.
this could be an example where you hurt performance....again, you really
need to understand RAID confiuration to make this stuff work right but
generally speaking, segregation via filegroups is not going to add over head
and reduce performance.
Greg Jackson
Portland, Oregon
We have db that has been configured w/ about 7 different filegroups.
Still trying to fully understand filegroups and their impact if tables
and indices aren't properly separated. I believe I read that you can
create different files for ea. index, but they must belong to the same
filegroup. In this example below they look to be in separate filegroups.
Is this a bad way of setting up filegroups? Am I multiplying the amt of
threads (exponentially) with this type of set up, which is causing my
i/o bottleneck?
Thank You!
C
ROWCT TBL KEY FILEGRP
15517322 TESTSHIFT X1_TESTSHIFT test1
15422280 TESTSHIFT PK_TESTSHIFT test1
15422280 TESTSHIFT X2_TESTSHIFT test3
15422280 TESTSHIFT X3_TESTSHIFT test4
15422280 TESTSHIFT X4_TESTSHIFT test1
13319460 SHIFT PK_SHIFT test4
13228153 SHIFT X1_SHIFT test1
13228153 SHIFT X2_SHIFT test1
13228153 SHIFT X3_SHIFT test1
13228153 SHIFT X4_SHIFT test1
13228153 SHIFT X5_SHIFT test2
13228153 SHIFT X6_SHIFT test1
10829933 TESTSHFTRC TESTSHFTRC test3
10748087 TESTSHFTRC X1_TESTSHFTRC test2
10748087 TESTSHFTRC X2_TESTSHFTRC test1
9105520 TEST_LOOKUP TEST_LOOKUP test4
8583208 TESTSHIFTSEG PK_TESTSHIFTSEG te
st2
8502786 TESTSHIFTSEG X1_TESTSHIFTSEG te
st1If all of these files (and hence filegroups) are on the same drive array it
won't much matter how many you have. If your array can not handle the I/O
load then adding more files will not make it any better. The biggest
advantage to splitting data and indexes into two (or more) filegroups is
only when the files in those filegroups live on separate physical drive
arrays.
Andrew J. Kelly SQL MVP
"Ooroster" <chris@.nyp.org> wrote in message
news:eFb6pvuDFHA.2876@.TK2MSFTNGP12.phx.gbl...
> Hi all,
> We have db that has been configured w/ about 7 different filegroups.
> Still trying to fully understand filegroups and their impact if tables and
> indices aren't properly separated. I believe I read that you can create
> different files for ea. index, but they must belong to the same filegroup.
> In this example below they look to be in separate filegroups. Is this a
> bad way of setting up filegroups? Am I multiplying the amt of threads
> (exponentially) with this type of set up, which is causing my i/o
> bottleneck?
> Thank You!
> C
> ROWCT TBL KEY FILEGRP
> 15517322 TESTSHIFT X1_TESTSHIFT test1
> 15422280 TESTSHIFT PK_TESTSHIFT test1
> 15422280 TESTSHIFT X2_TESTSHIFT test3
> 15422280 TESTSHIFT X3_TESTSHIFT test4
> 15422280 TESTSHIFT X4_TESTSHIFT test1
> 13319460 SHIFT PK_SHIFT test4
> 13228153 SHIFT X1_SHIFT test1
> 13228153 SHIFT X2_SHIFT test1
> 13228153 SHIFT X3_SHIFT test1
> 13228153 SHIFT X4_SHIFT test1
> 13228153 SHIFT X5_SHIFT test2
> 13228153 SHIFT X6_SHIFT test1
> 10829933 TESTSHFTRC TESTSHFTRC test3
> 10748087 TESTSHFTRC X1_TESTSHFTRC test2
> 10748087 TESTSHFTRC X2_TESTSHFTRC test1
> 9105520 TEST_LOOKUP TEST_LOOKUP test4
> 8583208 TESTSHIFTSEG PK_TESTSHIFTSEG test2
> 8502786 TESTSHIFTSEG X1_TESTSHIFTSEG test1|||Ahhhh, ok. Could there ever be an instance where you group data and
indices in a way that hurts performance even though the files have been
separated onto their own raid sets?
Thanks much for the info Andrew! It's been very helpful.|||Separating indexes and tables onto their own drive arrays should only
increase performance (or at worst case the same) over keeping them all
together on one array. That is as long as you don't remove drives from the
original array.
Andrew J. Kelly SQL MVP
"Ooroster" <chris@.nyp.org> wrote in message
news:u7UQ$ixDFHA.4032@.TK2MSFTNGP10.phx.gbl...
> Ahhhh, ok. Could there ever be an instance where you group data and
> indices in a way that hurts performance even though the files have been
> separated onto their own raid sets?
> Thanks much for the info Andrew! It's been very helpful.|||One example where you can hurt yourself with RAID volume segregation is when
you simply make Bad decisions on RAID layout.
For example:
you start with a single RAID 5 Volume with 4 disks and on that volume, you
place everything. OS, Logs, Data, Indexes.
You attempt to segregate data by creating seperate RAID Volumes: You create
2 RAID 5 Volumes with 2 disks each: placing OS and logs on One volume and
you place Data and indexes on the other volume.
You went from having a single decent volume (enough disks for striping and
parity) and you shoot yourself in the foot by creating two volumes that cant
really benefit from striping.
this could be an example where you hurt performance....again, you really
need to understand RAID confiuration to make this stuff work right but
generally speaking, segregation via filegroups is not going to add over head
and reduce performance.
Greg Jackson
Portland, Oregon
Performance hit using Compatibility?
To your knowledge, is there any performance hit with SQL 2005 when you set a database to a lower compatibility mode. IE from 9.0 to 7.0For the application I was working on, I noticed a 15%-20% speed hit when using 8.0 vs 9.0.
Performance hit on subscribers under merge replication
I have a setup where a single 1.5 million row table is published to 6
separate databases using merge replication. On 4 out of the 6 I have no
problem, but on 2 there seems to be an excessive performance hit on the
subscribing server's CPU when the distributor attempts to apply updates AND
the merge agents never complete running successfully.
Here are some more details:
The Publisher and Distributor are on same server (2x800MHz, very little load
on server)
All 6 subscriptions are Push subscription, with merge agent running once an
hour, there are no filters on the article
When there are changes to synchronize they are almost always Updates, and
thousands of rows will be updated at each subscriber within a few hours on a
Saturday night. Then there will be no activity all week so the 7 tables can
synchronize before the next Saturday.
Subscriber #1: on same LAN as publisher. Very fast to synchronize with
Publisher
Subscribers #2, 3 & 4: each on a different LANs from the Publisher with 256K
DSL connections to Publisher. Synchronize with Publisher within a few hours.
Subscribers #5 & 6: each on a different LANs from the Publisher with < 128K
DSL connections to Publisher. Never seem to synchronize with Publisher. The
merge agents' histories both show a long list of messages saying "100
updates downloaded" then "The process could not deliver update(s) at the
'Subscriber'. ... The merge process timed out while executing a query.
Reconfigure the QueryTimeout parameter and retry the operation."
Subscribers #5 & 6 are not very fast boxes (500 MHz Pentium III). They serve
another DB application and the users complain that this application has
become much slower since the replication was set up. I assume this is caused
by high CPU because the subscribing database and this application's
databases are separate (i.e. the slowdown is not caused by table locks being
held)
How can I minimize the hit on Subscribers #5 & 6 and ensure the updates are
delivered to the subscribing database?
I have not figured this problem out yet. To work around the performance hit
problem, I want to find a way to have the merge agent only run during the
night. I know how to schedule the agent to run between certain hours, but is
there a way to ensure the agent stops at 7AM if it is running at that time?
"Laurence Neville" <laurenceneville@.hotmail.com> wrote in message
news:%23Lvtc$DeFHA.2288@.TK2MSFTNGP14.phx.gbl...
>I have a setup where a single 1.5 million row table is published to 6
>separate databases using merge replication. On 4 out of the 6 I have no
>problem, but on 2 there seems to be an excessive performance hit on the
>subscribing server's CPU when the distributor attempts to apply updates AND
>the merge agents never complete running successfully.
> Here are some more details:
> The Publisher and Distributor are on same server (2x800MHz, very little
> load on server)
> All 6 subscriptions are Push subscription, with merge agent running once
> an hour, there are no filters on the article
> When there are changes to synchronize they are almost always Updates, and
> thousands of rows will be updated at each subscriber within a few hours on
> a Saturday night. Then there will be no activity all week so the 7 tables
> can synchronize before the next Saturday.
> Subscriber #1: on same LAN as publisher. Very fast to synchronize with
> Publisher
> Subscribers #2, 3 & 4: each on a different LANs from the Publisher with
> 256K DSL connections to Publisher. Synchronize with Publisher within a few
> hours.
> Subscribers #5 & 6: each on a different LANs from the Publisher with <
> 128K DSL connections to Publisher. Never seem to synchronize with
> Publisher. The merge agents' histories both show a long list of messages
> saying "100 updates downloaded" then "The process could not deliver
> update(s) at the 'Subscriber'. ... The merge process timed out while
> executing a query. Reconfigure the QueryTimeout parameter and retry the
> operation."
> Subscribers #5 & 6 are not very fast boxes (500 MHz Pentium III). They
> serve another DB application and the users complain that this application
> has become much slower since the replication was set up. I assume this is
> caused by high CPU because the subscribing database and this application's
> databases are separate (i.e. the slowdown is not caused by table locks
> being held)
> How can I minimize the hit on Subscribers #5 & 6 and ensure the updates
> are delivered to the subscribing database?
>
|||Laurence,
you could have another job that calls sp_stop_job, and which runs at 7am. I
have something similar for a job load which couldn't conflict with business
hours. The problem is that if the main job isn't running, the sp_stop_job
will fail and give the fail icon in EM. So, I check the job status prior to
deciding whether to stop or not. The Proc I've created is below.
Rgds,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
CREATE PROCEDURE spReturnJobState (@.JobID uniqueidentifier, @.JobState int
OUTPUT)
/************************************************** **************************************
Description: Script to return the job state of the data load job so
if it is running we can stop it.
Returns: (None)
Author: Paul Ibison (xt 26163)
Date Created: 22/12/2004
Revisions:
************************************************** ***************************************/
AS
CREATE TABLE #xp_results (job_id UNIQUEIDENTIFIER NOT NULL,
last_run_date INT NOT NULL,
last_run_time INT NOT NULL,
next_run_date INT NOT NULL,
next_run_time INT NOT NULL,
next_run_schedule_id INT NOT NULL,
requested_to_run INT NOT NULL, -- BOOL
request_source INT NOT NULL,
request_source_id sysname collate database_default
null,
running INT NOT NULL, -- BOOL
current_step INT NOT NULL,
current_retry_attempt INT NOT NULL,
job_state INT NOT NULL)
INSERT INTO #xp_results
EXECUTE master.dbo.xp_sqlagent_enum_jobs 1, 'dbo'
SELECT @.JobState = job_state FROM #xp_results WHERE job_id = @.JobID
DROP TABLE #xp_results
GO
declare @.retstatus int
exec dba_admin..spReturnJobState 'E2682241-48BC-479B-BD99-795712292720',
@.retstatus output
if @.retstatus = 1 --executing
begin
exec msdb..sp_stop_job 'UPLOAD_PROCESS_DATA_JOB'
end
|||Paul,
Thanks very much, I will use this.
Do you have any suggestions about my other problem: merge agents failing to
deliver updates to subscribers that have slow connections to the publisher,
also causing a high load on the subscriber CPU at the same time? Are there
configuration options I can set for slow connections, or to reduce the load
on the CPU?
Thanks
Laurence
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:uwGwsT8eFHA.3932@.TK2MSFTNGP12.phx.gbl...
> Laurence,
> you could have another job that calls sp_stop_job, and which runs at 7am.
> I have something similar for a job load which couldn't conflict with
> business hours. The problem is that if the main job isn't running, the
> sp_stop_job will fail and give the fail icon in EM. So, I check the job
> status prior to deciding whether to stop or not. The Proc I've created is
> below.
> Rgds,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
> CREATE PROCEDURE spReturnJobState (@.JobID uniqueidentifier, @.JobState int
> OUTPUT)
> /************************************************** **************************************
> Description: Script to return the job state of the data load job so
> if it is running we can stop it.
> Returns: (None)
> Author: Paul Ibison (xt 26163)
> Date Created: 22/12/2004
> Revisions:
> ************************************************** ***************************************/
> AS
> CREATE TABLE #xp_results (job_id UNIQUEIDENTIFIER NOT NULL,
> last_run_date INT NOT NULL,
> last_run_time INT NOT NULL,
> next_run_date INT NOT NULL,
> next_run_time INT NOT NULL,
> next_run_schedule_id INT NOT NULL,
> requested_to_run INT NOT NULL, -- BOOL
> request_source INT NOT NULL,
> request_source_id sysname collate database_default
> null,
> running INT NOT NULL, -- BOOL
> current_step INT NOT NULL,
> current_retry_attempt INT NOT NULL,
> job_state INT NOT NULL)
> INSERT INTO #xp_results
> EXECUTE master.dbo.xp_sqlagent_enum_jobs 1, 'dbo'
> SELECT @.JobState = job_state FROM #xp_results WHERE job_id = @.JobID
> DROP TABLE #xp_results
> GO
>
> declare @.retstatus int
> exec dba_admin..spReturnJobState 'E2682241-48BC-479B-BD99-795712292720',
> @.retstatus output
> if @.retstatus = 1 --executing
> begin
> exec msdb..sp_stop_job 'UPLOAD_PROCESS_DATA_JOB'
> end
>
|||Laurence,
what is the error when there is a failure?
Cheers,
Paul
"Laurence Neville" <laurenceneville@.hotmail.com> wrote in message
news:OSUG9oBfFHA.1148@.TK2MSFTNGP12.phx.gbl...
> Paul,
> Thanks very much, I will use this.
> Do you have any suggestions about my other problem: merge agents failing
> to deliver updates to subscribers that have slow connections to the
> publisher, also causing a high load on the subscriber CPU at the same
> time? Are there configuration options I can set for slow connections, or
> to reduce the load on the CPU?
> Thanks
> Laurence
> "Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
> news:uwGwsT8eFHA.3932@.TK2MSFTNGP12.phx.gbl...
>
|||The merge agents' histories both show a long list of messages saying "100
updates downloaded" then "The process could not deliver update(s) at the
'Subscriber'. ... The merge process timed out while executing a query.
Reconfigure the QueryTimeout parameter and retry the operation."
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:ug9mi3BfFHA.3936@.TK2MSFTNGP14.phx.gbl...
> Laurence,
> what is the error when there is a failure?
> Cheers,
> Paul
> "Laurence Neville" <laurenceneville@.hotmail.com> wrote in message
> news:OSUG9oBfFHA.1148@.TK2MSFTNGP12.phx.gbl...
>
|||OK - try creating a custom merge agent profile with a large value of
QueryTimeout and resynchronizing.
Cheers,
Paul Ibison
|||OK I am trying that out.
Question - like I said before, what I typically see in the agent history is
a long listof messages saying "100
updates downloaded" then "The process could not deliver update(s) at the
'Subscriber'. ..." When the agent runs again, do the updates that were
previously downloaded to the subscriber still exist on the subscriber (I
imagine them being in some "holding area" waiting to be applied to the
database) , or does the download start back at the beginning? In other
words, will all the updates eventually be downloaded (even if they are not
successfully applied to the database each time), or is the process always
going back to square one when it fails?
Thanks
Laurence
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:eTdCnqCfFHA.2372@.TK2MSFTNGP14.phx.gbl...
> OK - try creating a custom merge agent profile with a large value of
> QueryTimeout and resynchronizing.
> Cheers,
> Paul Ibison
>
|||Laurence,
Merge downloads the changes in batches and these batches are groups of
records which are applied as a transaction. An interruption to a batch will
cause a rollback of that particular batch while previous ones have been
already committed. Resynchronization will continue with the same batch
afterwards at the start of the batch and then do all the subsequent batches.
Rgds,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||When I see "100 updates downloaded" in the merge agent history, is that one
batch (i.e. 100 changes)? From reading Books Online that seems to be the
case ("By default, the Merge Agent processes 100 generations in each batch
uploaded and downloaded between the Publisher and Subscriber. ")
Next question: when I look at a given session's history and see 20 messages
saying "100 updates downloaded" then a single message saying "The process
could not deliver update(s) at the 'Subscriber'. ..." (either because the
merge process timed out, or because the job automatically gets stopped at
7AM - thanks for telling me how to do that!) does that mean that all 20
batches have failed to be applied and will be redownloaded in the next
session? If so, is there a way to limit the number of batches downloaded in
a session? I am looking for any techniques that will result in some changes
being applied to the subscriber, however slowly! I am also trying out the
Query Timeout parameter that you mentioned a few messages ago.
Thanks
Laurence
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:Or$gnsUfFHA.1148@.TK2MSFTNGP12.phx.gbl...
> Laurence,
> Merge downloads the changes in batches and these batches are groups of
> records which are applied as a transaction. An interruption to a batch
> will cause a rollback of that particular batch while previous ones have
> been already committed. Resynchronization will continue with the same
> batch afterwards at the start of the batch and then do all the subsequent
> batches.
> Rgds,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
>
separate databases using merge replication. On 4 out of the 6 I have no
problem, but on 2 there seems to be an excessive performance hit on the
subscribing server's CPU when the distributor attempts to apply updates AND
the merge agents never complete running successfully.
Here are some more details:
The Publisher and Distributor are on same server (2x800MHz, very little load
on server)
All 6 subscriptions are Push subscription, with merge agent running once an
hour, there are no filters on the article
When there are changes to synchronize they are almost always Updates, and
thousands of rows will be updated at each subscriber within a few hours on a
Saturday night. Then there will be no activity all week so the 7 tables can
synchronize before the next Saturday.
Subscriber #1: on same LAN as publisher. Very fast to synchronize with
Publisher
Subscribers #2, 3 & 4: each on a different LANs from the Publisher with 256K
DSL connections to Publisher. Synchronize with Publisher within a few hours.
Subscribers #5 & 6: each on a different LANs from the Publisher with < 128K
DSL connections to Publisher. Never seem to synchronize with Publisher. The
merge agents' histories both show a long list of messages saying "100
updates downloaded" then "The process could not deliver update(s) at the
'Subscriber'. ... The merge process timed out while executing a query.
Reconfigure the QueryTimeout parameter and retry the operation."
Subscribers #5 & 6 are not very fast boxes (500 MHz Pentium III). They serve
another DB application and the users complain that this application has
become much slower since the replication was set up. I assume this is caused
by high CPU because the subscribing database and this application's
databases are separate (i.e. the slowdown is not caused by table locks being
held)
How can I minimize the hit on Subscribers #5 & 6 and ensure the updates are
delivered to the subscribing database?
I have not figured this problem out yet. To work around the performance hit
problem, I want to find a way to have the merge agent only run during the
night. I know how to schedule the agent to run between certain hours, but is
there a way to ensure the agent stops at 7AM if it is running at that time?
"Laurence Neville" <laurenceneville@.hotmail.com> wrote in message
news:%23Lvtc$DeFHA.2288@.TK2MSFTNGP14.phx.gbl...
>I have a setup where a single 1.5 million row table is published to 6
>separate databases using merge replication. On 4 out of the 6 I have no
>problem, but on 2 there seems to be an excessive performance hit on the
>subscribing server's CPU when the distributor attempts to apply updates AND
>the merge agents never complete running successfully.
> Here are some more details:
> The Publisher and Distributor are on same server (2x800MHz, very little
> load on server)
> All 6 subscriptions are Push subscription, with merge agent running once
> an hour, there are no filters on the article
> When there are changes to synchronize they are almost always Updates, and
> thousands of rows will be updated at each subscriber within a few hours on
> a Saturday night. Then there will be no activity all week so the 7 tables
> can synchronize before the next Saturday.
> Subscriber #1: on same LAN as publisher. Very fast to synchronize with
> Publisher
> Subscribers #2, 3 & 4: each on a different LANs from the Publisher with
> 256K DSL connections to Publisher. Synchronize with Publisher within a few
> hours.
> Subscribers #5 & 6: each on a different LANs from the Publisher with <
> 128K DSL connections to Publisher. Never seem to synchronize with
> Publisher. The merge agents' histories both show a long list of messages
> saying "100 updates downloaded" then "The process could not deliver
> update(s) at the 'Subscriber'. ... The merge process timed out while
> executing a query. Reconfigure the QueryTimeout parameter and retry the
> operation."
> Subscribers #5 & 6 are not very fast boxes (500 MHz Pentium III). They
> serve another DB application and the users complain that this application
> has become much slower since the replication was set up. I assume this is
> caused by high CPU because the subscribing database and this application's
> databases are separate (i.e. the slowdown is not caused by table locks
> being held)
> How can I minimize the hit on Subscribers #5 & 6 and ensure the updates
> are delivered to the subscribing database?
>
|||Laurence,
you could have another job that calls sp_stop_job, and which runs at 7am. I
have something similar for a job load which couldn't conflict with business
hours. The problem is that if the main job isn't running, the sp_stop_job
will fail and give the fail icon in EM. So, I check the job status prior to
deciding whether to stop or not. The Proc I've created is below.
Rgds,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
CREATE PROCEDURE spReturnJobState (@.JobID uniqueidentifier, @.JobState int
OUTPUT)
/************************************************** **************************************
Description: Script to return the job state of the data load job so
if it is running we can stop it.
Returns: (None)
Author: Paul Ibison (xt 26163)
Date Created: 22/12/2004
Revisions:
************************************************** ***************************************/
AS
CREATE TABLE #xp_results (job_id UNIQUEIDENTIFIER NOT NULL,
last_run_date INT NOT NULL,
last_run_time INT NOT NULL,
next_run_date INT NOT NULL,
next_run_time INT NOT NULL,
next_run_schedule_id INT NOT NULL,
requested_to_run INT NOT NULL, -- BOOL
request_source INT NOT NULL,
request_source_id sysname collate database_default
null,
running INT NOT NULL, -- BOOL
current_step INT NOT NULL,
current_retry_attempt INT NOT NULL,
job_state INT NOT NULL)
INSERT INTO #xp_results
EXECUTE master.dbo.xp_sqlagent_enum_jobs 1, 'dbo'
SELECT @.JobState = job_state FROM #xp_results WHERE job_id = @.JobID
DROP TABLE #xp_results
GO
declare @.retstatus int
exec dba_admin..spReturnJobState 'E2682241-48BC-479B-BD99-795712292720',
@.retstatus output
if @.retstatus = 1 --executing
begin
exec msdb..sp_stop_job 'UPLOAD_PROCESS_DATA_JOB'
end
|||Paul,
Thanks very much, I will use this.
Do you have any suggestions about my other problem: merge agents failing to
deliver updates to subscribers that have slow connections to the publisher,
also causing a high load on the subscriber CPU at the same time? Are there
configuration options I can set for slow connections, or to reduce the load
on the CPU?
Thanks
Laurence
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:uwGwsT8eFHA.3932@.TK2MSFTNGP12.phx.gbl...
> Laurence,
> you could have another job that calls sp_stop_job, and which runs at 7am.
> I have something similar for a job load which couldn't conflict with
> business hours. The problem is that if the main job isn't running, the
> sp_stop_job will fail and give the fail icon in EM. So, I check the job
> status prior to deciding whether to stop or not. The Proc I've created is
> below.
> Rgds,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
> CREATE PROCEDURE spReturnJobState (@.JobID uniqueidentifier, @.JobState int
> OUTPUT)
> /************************************************** **************************************
> Description: Script to return the job state of the data load job so
> if it is running we can stop it.
> Returns: (None)
> Author: Paul Ibison (xt 26163)
> Date Created: 22/12/2004
> Revisions:
> ************************************************** ***************************************/
> AS
> CREATE TABLE #xp_results (job_id UNIQUEIDENTIFIER NOT NULL,
> last_run_date INT NOT NULL,
> last_run_time INT NOT NULL,
> next_run_date INT NOT NULL,
> next_run_time INT NOT NULL,
> next_run_schedule_id INT NOT NULL,
> requested_to_run INT NOT NULL, -- BOOL
> request_source INT NOT NULL,
> request_source_id sysname collate database_default
> null,
> running INT NOT NULL, -- BOOL
> current_step INT NOT NULL,
> current_retry_attempt INT NOT NULL,
> job_state INT NOT NULL)
> INSERT INTO #xp_results
> EXECUTE master.dbo.xp_sqlagent_enum_jobs 1, 'dbo'
> SELECT @.JobState = job_state FROM #xp_results WHERE job_id = @.JobID
> DROP TABLE #xp_results
> GO
>
> declare @.retstatus int
> exec dba_admin..spReturnJobState 'E2682241-48BC-479B-BD99-795712292720',
> @.retstatus output
> if @.retstatus = 1 --executing
> begin
> exec msdb..sp_stop_job 'UPLOAD_PROCESS_DATA_JOB'
> end
>
|||Laurence,
what is the error when there is a failure?
Cheers,
Paul
"Laurence Neville" <laurenceneville@.hotmail.com> wrote in message
news:OSUG9oBfFHA.1148@.TK2MSFTNGP12.phx.gbl...
> Paul,
> Thanks very much, I will use this.
> Do you have any suggestions about my other problem: merge agents failing
> to deliver updates to subscribers that have slow connections to the
> publisher, also causing a high load on the subscriber CPU at the same
> time? Are there configuration options I can set for slow connections, or
> to reduce the load on the CPU?
> Thanks
> Laurence
> "Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
> news:uwGwsT8eFHA.3932@.TK2MSFTNGP12.phx.gbl...
>
|||The merge agents' histories both show a long list of messages saying "100
updates downloaded" then "The process could not deliver update(s) at the
'Subscriber'. ... The merge process timed out while executing a query.
Reconfigure the QueryTimeout parameter and retry the operation."
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:ug9mi3BfFHA.3936@.TK2MSFTNGP14.phx.gbl...
> Laurence,
> what is the error when there is a failure?
> Cheers,
> Paul
> "Laurence Neville" <laurenceneville@.hotmail.com> wrote in message
> news:OSUG9oBfFHA.1148@.TK2MSFTNGP12.phx.gbl...
>
|||OK - try creating a custom merge agent profile with a large value of
QueryTimeout and resynchronizing.
Cheers,
Paul Ibison
|||OK I am trying that out.
Question - like I said before, what I typically see in the agent history is
a long listof messages saying "100
updates downloaded" then "The process could not deliver update(s) at the
'Subscriber'. ..." When the agent runs again, do the updates that were
previously downloaded to the subscriber still exist on the subscriber (I
imagine them being in some "holding area" waiting to be applied to the
database) , or does the download start back at the beginning? In other
words, will all the updates eventually be downloaded (even if they are not
successfully applied to the database each time), or is the process always
going back to square one when it fails?
Thanks
Laurence
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:eTdCnqCfFHA.2372@.TK2MSFTNGP14.phx.gbl...
> OK - try creating a custom merge agent profile with a large value of
> QueryTimeout and resynchronizing.
> Cheers,
> Paul Ibison
>
|||Laurence,
Merge downloads the changes in batches and these batches are groups of
records which are applied as a transaction. An interruption to a batch will
cause a rollback of that particular batch while previous ones have been
already committed. Resynchronization will continue with the same batch
afterwards at the start of the batch and then do all the subsequent batches.
Rgds,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||When I see "100 updates downloaded" in the merge agent history, is that one
batch (i.e. 100 changes)? From reading Books Online that seems to be the
case ("By default, the Merge Agent processes 100 generations in each batch
uploaded and downloaded between the Publisher and Subscriber. ")
Next question: when I look at a given session's history and see 20 messages
saying "100 updates downloaded" then a single message saying "The process
could not deliver update(s) at the 'Subscriber'. ..." (either because the
merge process timed out, or because the job automatically gets stopped at
7AM - thanks for telling me how to do that!) does that mean that all 20
batches have failed to be applied and will be redownloaded in the next
session? If so, is there a way to limit the number of batches downloaded in
a session? I am looking for any techniques that will result in some changes
being applied to the subscriber, however slowly! I am also trying out the
Query Timeout parameter that you mentioned a few messages ago.
Thanks
Laurence
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:Or$gnsUfFHA.1148@.TK2MSFTNGP12.phx.gbl...
> Laurence,
> Merge downloads the changes in batches and these batches are groups of
> records which are applied as a transaction. An interruption to a batch
> will cause a rollback of that particular batch while previous ones have
> been already committed. Resynchronization will continue with the same
> batch afterwards at the start of the batch and then do all the subsequent
> batches.
> Rgds,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
>
Performance hit if I use SELECT DISTINCT?
We use an ASP/MS SQL 2000 system to send out our mass e-mailing to about 3,500 subscribers (and the list is growing). There are some duplicate entries in the DB and I was thinking about using this code
SELECT DISTINCT email FROM Subscribers
to remove the duplicates (at least until we can get around to cleaning up the data and then putting up new subscriber form to prevent duplicate entries).
I was wondering, though... Will this have a significant impact on our performance? I mean, that's a lot of e-mail addresses to process and I don't want to bog our system down unnecessarily.
What do you performance gurus think?Hi
Distinct does have an overhead but against 3500 records it should go unnoticed I would expect. Suck it an see - test both queries and see how they hold up.
If you are only selecting email and email is indexed (nonclustered) then you have a covered query (the data comes from the index). These values are not duplicated so I would imagine (never tested) that there would be 0 overhead for this. Someone could confirm or correct this. Either way - the overhead should be minimal.
HTH|||Thanks, I also don't think there will be a hit on this small a recordset, but I wanted to ask. As for indexing and other modifications... not today. I need a 30-second solution to this problem created in someone else's old code. These issues will be addressed in our next upgrade, which should be done next month.
Thanks again!|||DISTINCT can have a significant impact on performance, but again this will only be noticable on large datasets.sql
SELECT DISTINCT email FROM Subscribers
to remove the duplicates (at least until we can get around to cleaning up the data and then putting up new subscriber form to prevent duplicate entries).
I was wondering, though... Will this have a significant impact on our performance? I mean, that's a lot of e-mail addresses to process and I don't want to bog our system down unnecessarily.
What do you performance gurus think?Hi
Distinct does have an overhead but against 3500 records it should go unnoticed I would expect. Suck it an see - test both queries and see how they hold up.
If you are only selecting email and email is indexed (nonclustered) then you have a covered query (the data comes from the index). These values are not duplicated so I would imagine (never tested) that there would be 0 overhead for this. Someone could confirm or correct this. Either way - the overhead should be minimal.
HTH|||Thanks, I also don't think there will be a hit on this small a recordset, but I wanted to ask. As for indexing and other modifications... not today. I need a 30-second solution to this problem created in someone else's old code. These issues will be addressed in our next upgrade, which should be done next month.
Thanks again!|||DISTINCT can have a significant impact on performance, but again this will only be noticable on large datasets.sql
Performance hit for cross-database partitioned view (on same serve
What would be the performance difference for a view spanning multiple tables
in separate filegroups but in the same database as opposed to the same view
spanning tables in different databases (all on the same server).
Since the execution plan is the same, I would think that there is no
difference but some of the tests I have done seem to indicuate a performance
hit of about 10% for the cross-database view.
If there are updates, then there are cost related to transaction commit
cross databases.
You might want to use set statistic IO to see more details of the query
execution.
--
Wei Xiao [MSFT]
SQL Server Storage Engine Development
http://blogs.msdn.com/weix
This posting is provided "AS IS" with no warranties, and confers no rights.
"DBA72" <DBA72@.discussions.microsoft.com> wrote in message
news:C764B4BA-4C3E-41B4-BC41-E85A894255EC@.microsoft.com...
> What would be the performance difference for a view spanning multiple
tables
> in separate filegroups but in the same database as opposed to the same
view
> spanning tables in different databases (all on the same server).
> Since the execution plan is the same, I would think that there is no
> difference but some of the tests I have done seem to indicuate a
performance
> hit of about 10% for the cross-database view.
|||If the databases are on the same server and running under the same instance
of SQL Server then the difference should be very small. Running under
different instances (even on the same server) requires cross-process
marshalling and incurrs a performance impact.
"DBA72" wrote:
> What would be the performance difference for a view spanning multiple tables
> in separate filegroups but in the same database as opposed to the same view
> spanning tables in different databases (all on the same server).
> Since the execution plan is the same, I would think that there is no
> difference but some of the tests I have done seem to indicuate a performance
> hit of about 10% for the cross-database view.
in separate filegroups but in the same database as opposed to the same view
spanning tables in different databases (all on the same server).
Since the execution plan is the same, I would think that there is no
difference but some of the tests I have done seem to indicuate a performance
hit of about 10% for the cross-database view.
If there are updates, then there are cost related to transaction commit
cross databases.
You might want to use set statistic IO to see more details of the query
execution.
--
Wei Xiao [MSFT]
SQL Server Storage Engine Development
http://blogs.msdn.com/weix
This posting is provided "AS IS" with no warranties, and confers no rights.
"DBA72" <DBA72@.discussions.microsoft.com> wrote in message
news:C764B4BA-4C3E-41B4-BC41-E85A894255EC@.microsoft.com...
> What would be the performance difference for a view spanning multiple
tables
> in separate filegroups but in the same database as opposed to the same
view
> spanning tables in different databases (all on the same server).
> Since the execution plan is the same, I would think that there is no
> difference but some of the tests I have done seem to indicuate a
performance
> hit of about 10% for the cross-database view.
|||If the databases are on the same server and running under the same instance
of SQL Server then the difference should be very small. Running under
different instances (even on the same server) requires cross-process
marshalling and incurrs a performance impact.
"DBA72" wrote:
> What would be the performance difference for a view spanning multiple tables
> in separate filegroups but in the same database as opposed to the same view
> spanning tables in different databases (all on the same server).
> Since the execution plan is the same, I would think that there is no
> difference but some of the tests I have done seem to indicuate a performance
> hit of about 10% for the cross-database view.
Performance hit for cross-database partitioned view (on same serve
What would be the performance difference for a view spanning multiple tables
in separate filegroups but in the same database as opposed to the same view
spanning tables in different databases (all on the same server).
Since the execution plan is the same, I would think that there is no
difference but some of the tests I have done seem to indicuate a performance
hit of about 10% for the cross-database view.If there are updates, then there are cost related to transaction commit
cross databases.
You might want to use set statistic IO to see more details of the query
execution.
--
Wei Xiao [MSFT]
SQL Server Storage Engine Development
http://blogs.msdn.com/weix
This posting is provided "AS IS" with no warranties, and confers no rights.
"DBA72" <DBA72@.discussions.microsoft.com> wrote in message
news:C764B4BA-4C3E-41B4-BC41-E85A894255EC@.microsoft.com...
> What would be the performance difference for a view spanning multiple
tables
> in separate filegroups but in the same database as opposed to the same
view
> spanning tables in different databases (all on the same server).
> Since the execution plan is the same, I would think that there is no
> difference but some of the tests I have done seem to indicuate a
performance
> hit of about 10% for the cross-database view.|||If the databases are on the same server and running under the same instance
of SQL Server then the difference should be very small. Running under
different instances (even on the same server) requires cross-process
marshalling and incurrs a performance impact.
"DBA72" wrote:
> What would be the performance difference for a view spanning multiple tabl
es
> in separate filegroups but in the same database as opposed to the same vie
w
> spanning tables in different databases (all on the same server).
> Since the execution plan is the same, I would think that there is no
> difference but some of the tests I have done seem to indicuate a performan
ce
> hit of about 10% for the cross-database view.
in separate filegroups but in the same database as opposed to the same view
spanning tables in different databases (all on the same server).
Since the execution plan is the same, I would think that there is no
difference but some of the tests I have done seem to indicuate a performance
hit of about 10% for the cross-database view.If there are updates, then there are cost related to transaction commit
cross databases.
You might want to use set statistic IO to see more details of the query
execution.
--
Wei Xiao [MSFT]
SQL Server Storage Engine Development
http://blogs.msdn.com/weix
This posting is provided "AS IS" with no warranties, and confers no rights.
"DBA72" <DBA72@.discussions.microsoft.com> wrote in message
news:C764B4BA-4C3E-41B4-BC41-E85A894255EC@.microsoft.com...
> What would be the performance difference for a view spanning multiple
tables
> in separate filegroups but in the same database as opposed to the same
view
> spanning tables in different databases (all on the same server).
> Since the execution plan is the same, I would think that there is no
> difference but some of the tests I have done seem to indicuate a
performance
> hit of about 10% for the cross-database view.|||If the databases are on the same server and running under the same instance
of SQL Server then the difference should be very small. Running under
different instances (even on the same server) requires cross-process
marshalling and incurrs a performance impact.
"DBA72" wrote:
> What would be the performance difference for a view spanning multiple tabl
es
> in separate filegroups but in the same database as opposed to the same vie
w
> spanning tables in different databases (all on the same server).
> Since the execution plan is the same, I would think that there is no
> difference but some of the tests I have done seem to indicuate a performan
ce
> hit of about 10% for the cross-database view.
Performance hit for cross-database partitioned view (on same serve
What would be the performance difference for a view spanning multiple tables
in separate filegroups but in the same database as opposed to the same view
spanning tables in different databases (all on the same server).
Since the execution plan is the same, I would think that there is no
difference but some of the tests I have done seem to indicuate a performance
hit of about 10% for the cross-database view.If there are updates, then there are cost related to transaction commit
cross databases.
You might want to use set statistic IO to see more details of the query
execution.
--
--
Wei Xiao [MSFT]
SQL Server Storage Engine Development
http://blogs.msdn.com/weix
This posting is provided "AS IS" with no warranties, and confers no rights.
"DBA72" <DBA72@.discussions.microsoft.com> wrote in message
news:C764B4BA-4C3E-41B4-BC41-E85A894255EC@.microsoft.com...
> What would be the performance difference for a view spanning multiple
tables
> in separate filegroups but in the same database as opposed to the same
view
> spanning tables in different databases (all on the same server).
> Since the execution plan is the same, I would think that there is no
> difference but some of the tests I have done seem to indicuate a
performance
> hit of about 10% for the cross-database view.|||If the databases are on the same server and running under the same instance
of SQL Server then the difference should be very small. Running under
different instances (even on the same server) requires cross-process
marshalling and incurrs a performance impact.
"DBA72" wrote:
> What would be the performance difference for a view spanning multiple tables
> in separate filegroups but in the same database as opposed to the same view
> spanning tables in different databases (all on the same server).
> Since the execution plan is the same, I would think that there is no
> difference but some of the tests I have done seem to indicuate a performance
> hit of about 10% for the cross-database view.
in separate filegroups but in the same database as opposed to the same view
spanning tables in different databases (all on the same server).
Since the execution plan is the same, I would think that there is no
difference but some of the tests I have done seem to indicuate a performance
hit of about 10% for the cross-database view.If there are updates, then there are cost related to transaction commit
cross databases.
You might want to use set statistic IO to see more details of the query
execution.
--
--
Wei Xiao [MSFT]
SQL Server Storage Engine Development
http://blogs.msdn.com/weix
This posting is provided "AS IS" with no warranties, and confers no rights.
"DBA72" <DBA72@.discussions.microsoft.com> wrote in message
news:C764B4BA-4C3E-41B4-BC41-E85A894255EC@.microsoft.com...
> What would be the performance difference for a view spanning multiple
tables
> in separate filegroups but in the same database as opposed to the same
view
> spanning tables in different databases (all on the same server).
> Since the execution plan is the same, I would think that there is no
> difference but some of the tests I have done seem to indicuate a
performance
> hit of about 10% for the cross-database view.|||If the databases are on the same server and running under the same instance
of SQL Server then the difference should be very small. Running under
different instances (even on the same server) requires cross-process
marshalling and incurrs a performance impact.
"DBA72" wrote:
> What would be the performance difference for a view spanning multiple tables
> in separate filegroups but in the same database as opposed to the same view
> spanning tables in different databases (all on the same server).
> Since the execution plan is the same, I would think that there is no
> difference but some of the tests I have done seem to indicuate a performance
> hit of about 10% for the cross-database view.
Performance Hit after upgrading to SQL 2005
I am hit with bad performance after upgrading my sql 2000 to sql 2005 version. All the infrastructure remains expect the performance :(
Please provide any pointers /guidelines so that I will check the same.
Thanks in Advance
Varun
I am seeing a huge performance problem right now.
I have to tagged to the TempDB right now. It seems to be leaving leftover objects and making the TempDB a mess. Check yours for a bunch of "#" + 8 digit Hex
If I stop and restart my SQL Services, which redoes the TempDB....I am good to go for a day or 2...then the same old problem. I have called MS about this but I am waiting for response from them
select * from tempdb.dbo.sysobjects where name like '#%' order by crdate desc
|||
What is the problem with performance, is it a particular query?
Have you rebuilt your indexes? This is a must do excercise after upgrading to SQL 2005.
|||I had the same problem -- we got a new server to run sql server 2005. It was a major upgrade over the old server. When we started testing our code on the new machine, we found that a significant amount of our code ran a lot slower on the new machine with sql 2005.
Eventually we were able to change our code, mostly through trial and error, to get the performance we expected. In almost every case, the performance problems were caused by:
1. Sub-queries - apparently the sql 2005 optimizer isn't as good as the sql 2000 optimizer at figuring out when the sub-query needs to be re-evaluated -- so large recordsets that join to a sub-query can be really slow since the sub-query sometimes gets re-evaluated for each record it's being joined to even when the sub-query generates the exact same result for each parent record.
2. Table variables - when we substituted #temp tables for table variables, performance increased dramatically -- in some cases putting a primary key on the table variable fixed the problem.
Unfortunately, we had to spend a couple of weeks to change all of our code, but eventually we were able to fix the performance problems.
Hope that helps.
performance hit after installing Oracle client?
SQL Server 2000 Enterprise Edition clustered on Win 2k Advanced Server
Last week we installed the Oracle client 8.1.6 to allow DTS to extract data
from our Oracle Financials system, which has been working fine. Since then u
sers have reported poor performance. Performance monitor showed low (20-30%)
CPU usage, 99.9% buffer ca
che hit ratio, usual disk utilisation amounts, but Pages/sec was averaging a
round 300. I understand this should be under 20. There is nothing else runni
ng on this server. We took the server down and switched to another node whic
h did not have the Oracle c
lient loaded and performance is back to usual. Has anyone else had a similar
problem, or is the Oracle software just a red herring?
Scott Doughtykevmc@.online.microsoft.com (Kevin McDonnell [MSFT]) wrote in message news:<ByFbcLS4DHA.568@.cpmsftngxa07.
phx.gbl>...
Hi Kevin
We only had one instance running. We switched to another node which
didn't have the Oracle client installed and all was fine. As I say,
the oracle software may not be relevant. If it helps, while the paging
was running high, the amount of free RAM was over 200Mb.
Scottsql
Last week we installed the Oracle client 8.1.6 to allow DTS to extract data
from our Oracle Financials system, which has been working fine. Since then u
sers have reported poor performance. Performance monitor showed low (20-30%)
CPU usage, 99.9% buffer ca
che hit ratio, usual disk utilisation amounts, but Pages/sec was averaging a
round 300. I understand this should be under 20. There is nothing else runni
ng on this server. We took the server down and switched to another node whic
h did not have the Oracle c
lient loaded and performance is back to usual. Has anyone else had a similar
problem, or is the Oracle software just a red herring?
Scott Doughtykevmc@.online.microsoft.com (Kevin McDonnell [MSFT]) wrote in message news:<ByFbcLS4DHA.568@.cpmsftngxa07.
phx.gbl>...
quote:
> When the performance was poor did you have multiple instances of SQL
> running on the same node?
> Did the performance improve after moving a single instance of SQL to
> another node?
> Thanks,
> Kevin McDonnell
> Microsoft Corporation
> This posting is provided AS IS with no warranties, and confers no rights.
Hi Kevin
We only had one instance running. We switched to another node which
didn't have the Oracle client installed and all was fine. As I say,
the oracle software may not be relevant. If it helps, while the paging
was running high, the amount of free RAM was over 200Mb.
Scottsql
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)
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)
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)
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)
Subscribe to:
Posts (Atom)