I have two servers - a web server and a data server. I have a web service on
my web server that receives a request for information on anywhere from 1 to
60,000 products. I'm looking at a couple of different approaches for
querying the database for the product information on the 1 to 60,000
products.
1) Pass the product list into a SQL stored procedure as a single delimited
string and have that SQL stored procedure do a SELECT * FROM Products WHERE
Sku IN ('sku1', 'sku2', 'sku3',..., 'sku60000'). I'm not sure of all of the
details of how I'll get from my single delimited string to that SQL query,
but I assume I can do it.
2) Loop through the list of 1 to 60,000 products and issue 1 to 60,000 calls
to a SQL stored procedure that returns data for a single product. My client
that's issuing the requests is multi-threaded and I'm comfortable that I can
set this up so that I'm issuing the 60,000 calls 5-10 or more calls at a
time.
The client that's requesting the 1 to 60,000 products is not very time
sensitive. It's a batch product feed process that runs 1 to 4 times a day.
The database I'm querying also supports a web site that does have real-time
requirements, so I don't want to structure my query in such a way that my
web service is negatively impacting the performance of my web site.
I'm trying to understand the pros and cons of the two approaches and would
appreciate any inputs. Thoughts I have so far:
Option 1 may perform better for the product feed client because it has one
large network transaction instead of 1 to 60,000 small network transactions.
But Option 1 may put a more intense load on the SQL server for a period of
time, potentially negatively impacting the real-time performance of the web
site.
With either solution I probably want to look at ways to ensure that the
request for data for 1 to 60,000 products is done at a lower priority than
real-time requests from the web site.
Any thoughts or suggestions?
Thanks,
ZoeZoe,
Have a look at this link to see how to pass in a delimited list and process
it appropriately. But I would probably create 2 or more stored procedures to
handle the different ranges of product requests so you can get a proper
query plan for each. For instance if you only had one product specified you
can easily do an index seek and get a good plan. But to retrieve 60K
products you may need to do a scan or Merge Join or even Hash Join. Due to
parameter sniffing if the first time the proc was run it had 50K products
you would get a plan for that many rows. But when you call it the next time
even with 1 product you will still get the same plan as before. So I would
have your app decide how may products there will be and call one of 2 or
more (depends on how many different query plans you may encounter) sps so
they each get their own plan. The case where they only lookup a single
product you can use a straight forward query with an =. More than 1 you need
to use dynamic sql or parse it into a table with a UDF.
http://www.sommarskog.se/arrays-in-sql.html
--
Andrew J. Kelly SQL MVP
Solid Quality Mentors
"Zoe Hart" <zoe.hart@.nospam.competitive.com> wrote in message
news:eNn1t%235UIHA.1480@.TK2MSFTNGP06.phx.gbl...
>I have two servers - a web server and a data server. I have a web service
>on my web server that receives a request for information on anywhere from 1
>to 60,000 products. I'm looking at a couple of different approaches for
>querying the database for the product information on the 1 to 60,000
>products.
> 1) Pass the product list into a SQL stored procedure as a single delimited
> string and have that SQL stored procedure do a SELECT * FROM Products
> WHERE Sku IN ('sku1', 'sku2', 'sku3',..., 'sku60000'). I'm not sure of all
> of the details of how I'll get from my single delimited string to that SQL
> query, but I assume I can do it.
> 2) Loop through the list of 1 to 60,000 products and issue 1 to 60,000
> calls to a SQL stored procedure that returns data for a single product. My
> client that's issuing the requests is multi-threaded and I'm comfortable
> that I can set this up so that I'm issuing the 60,000 calls 5-10 or more
> calls at a time.
> The client that's requesting the 1 to 60,000 products is not very time
> sensitive. It's a batch product feed process that runs 1 to 4 times a day.
> The database I'm querying also supports a web site that does have
> real-time requirements, so I don't want to structure my query in such a
> way that my web service is negatively impacting the performance of my web
> site.
> I'm trying to understand the pros and cons of the two approaches and would
> appreciate any inputs. Thoughts I have so far:
> Option 1 may perform better for the product feed client because it has one
> large network transaction instead of 1 to 60,000 small network
> transactions. But Option 1 may put a more intense load on the SQL server
> for a period of time, potentially negatively impacting the real-time
> performance of the web site.
> With either solution I probably want to look at ways to ensure that the
> request for data for 1 to 60,000 products is done at a lower priority than
> real-time requests from the web site.
> Any thoughts or suggestions?
> Thanks,
> Zoe
>|||"Zoe Hart" <zoe.hart@.nospam.competitive.com> wrote in message
news:eNn1t%235UIHA.1480@.TK2MSFTNGP06.phx.gbl...
>I have two servers - a web server and a data server. I have a web service
>on my web server that receives a request for information on anywhere from 1
>to 60,000 products. I'm looking at a couple of different approaches for
>querying the database for the product information on the 1 to 60,000
>products.
> 1) Pass the product list into a SQL stored procedure as a single delimited
> string and have that SQL stored procedure do a SELECT * FROM Products
> WHERE Sku IN ('sku1', 'sku2', 'sku3',..., 'sku60000'). I'm not sure of all
> of the details of how I'll get from my single delimited string to that SQL
> query, but I assume I can do it.
> 2) Loop through the list of 1 to 60,000 products and issue 1 to 60,000
> calls to a SQL stored procedure that returns data for a single product. My
> client that's issuing the requests is multi-threaded and I'm comfortable
> that I can set this up so that I'm issuing the 60,000 calls 5-10 or more
> calls at a time.
> The client that's requesting the 1 to 60,000 products is not very time
> sensitive. It's a batch product feed process that runs 1 to 4 times a day.
> The database I'm querying also supports a web site that does have
> real-time requirements, so I don't want to structure my query in such a
> way that my web service is negatively impacting the performance of my web
> site.
> I'm trying to understand the pros and cons of the two approaches and would
> appreciate any inputs. Thoughts I have so far:
> Option 1 may perform better for the product feed client because it has one
> large network transaction instead of 1 to 60,000 small network
> transactions. But Option 1 may put a more intense load on the SQL server
> for a period of time, potentially negatively impacting the real-time
> performance of the web site.
> With either solution I probably want to look at ways to ensure that the
> request for data for 1 to 60,000 products is done at a lower priority than
> real-time requests from the web site.
> Any thoughts or suggestions?
> Thanks,
> Zoe
>
Zoe,
Maybe another alternative is to use an XML as a parameter so you can load
easily this XML in a table, add indexes or whatever you need, and do a join
with your product table. I don't recommend option 2 (60000 calls) due to
network overhead and latency. It will be A LOT slower than the one call
alternative.
--
Rubén Garrigós
Solid Quality Mentors|||Zoe Hart (zoe.hart@.nospam.competitive.com) writes:
> 1) Pass the product list into a SQL stored procedure as a single
> delimited string and have that SQL stored procedure do a SELECT * FROM
> Products WHERE Sku IN ('sku1', 'sku2', 'sku3',..., 'sku60000'). I'm not
> sure of all of the details of how I'll get from my single delimited
> string to that SQL query, but I assume I can do it.
Whatever, don't do exactly this. The time it would take to compile
that statement is amazing, particularly if you are on SQL 2000.
But there are other alternatives, as I discuss in my article
http://www.sommarskog.se/arrays-in-sql.html.
Andrew made an important point about the need for different plans due
to the number of elements in the list. I think the best approach is to
unpack the list into a temp table, as this will cause a recompile
and the actual join is likely to use the best plan.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx|||Thanks for all the good input. Our rough plan at this point is to have a
stored procedure that accepts an XML input that contains the 1-n SKUs. That
stored procedure will create a temporary table that with columns for the SKU
and the other data we intend to look up. The stored procedure will write the
1-n SKUs to the temporary table and then use UPDATE FROM to join the
temporary table to one or more tables that contain the data we need and
update the columns in the temporary table. We'll then SELECT * from the
temporary table FOR XML to get our results. We can either go with that
result as is or map it to a format we like better in the code that calls the
stored proc.
Thanks again.
Zoe
"Zoe Hart" <zoe.hart@.nospam.competitive.com> wrote in message
news:eNn1t%235UIHA.1480@.TK2MSFTNGP06.phx.gbl...
>I have two servers - a web server and a data server. I have a web service
>on my web server that receives a request for information on anywhere from 1
>to 60,000 products. I'm looking at a couple of different approaches for
>querying the database for the product information on the 1 to 60,000
>products.
> 1) Pass the product list into a SQL stored procedure as a single delimited
> string and have that SQL stored procedure do a SELECT * FROM Products
> WHERE Sku IN ('sku1', 'sku2', 'sku3',..., 'sku60000'). I'm not sure of all
> of the details of how I'll get from my single delimited string to that SQL
> query, but I assume I can do it.
> 2) Loop through the list of 1 to 60,000 products and issue 1 to 60,000
> calls to a SQL stored procedure that returns data for a single product. My
> client that's issuing the requests is multi-threaded and I'm comfortable
> that I can set this up so that I'm issuing the 60,000 calls 5-10 or more
> calls at a time.
> The client that's requesting the 1 to 60,000 products is not very time
> sensitive. It's a batch product feed process that runs 1 to 4 times a day.
> The database I'm querying also supports a web site that does have
> real-time requirements, so I don't want to structure my query in such a
> way that my web service is negatively impacting the performance of my web
> site.
> I'm trying to understand the pros and cons of the two approaches and would
> appreciate any inputs. Thoughts I have so far:
> Option 1 may perform better for the product feed client because it has one
> large network transaction instead of 1 to 60,000 small network
> transactions. But Option 1 may put a more intense load on the SQL server
> for a period of time, potentially negatively impacting the real-time
> performance of the web site.
> With either solution I probably want to look at ways to ensure that the
> request for data for 1 to 60,000 products is done at a lower priority than
> real-time requests from the web site.
> Any thoughts or suggestions?
> Thanks,
> Zoe
>|||Sounds like about the best available approach.
Otherwise, if the query list is really that long, you might upload a
flat file to the server and then import it with an SSIS package.
Note that the numbers, 20k, 60k, are very tiny numbers for data in
terms of modern SQL Server performance, they are just very large
numbers if you try to list them all as part of a SQL command!
Josh
On Tue, 15 Jan 2008 09:52:25 -0500, "Zoe Hart"
<zoe.hart@.nospam.competitive.com> wrote:
>Thanks for all the good input. Our rough plan at this point is to have a
>stored procedure that accepts an XML input that contains the 1-n SKUs. That
>stored procedure will create a temporary table that with columns for the SKU
>and the other data we intend to look up. The stored procedure will write the
>1-n SKUs to the temporary table and then use UPDATE FROM to join the
>temporary table to one or more tables that contain the data we need and
>update the columns in the temporary table. We'll then SELECT * from the
>temporary table FOR XML to get our results. We can either go with that
>result as is or map it to a format we like better in the code that calls the
>stored proc.
>Thanks again.
>Zoe
>"Zoe Hart" <zoe.hart@.nospam.competitive.com> wrote in message
>news:eNn1t%235UIHA.1480@.TK2MSFTNGP06.phx.gbl...
>>I have two servers - a web server and a data server. I have a web service
>>on my web server that receives a request for information on anywhere from 1
>>to 60,000 products. I'm looking at a couple of different approaches for
>>querying the database for the product information on the 1 to 60,000
>>products.
>> 1) Pass the product list into a SQL stored procedure as a single delimited
>> string and have that SQL stored procedure do a SELECT * FROM Products
>> WHERE Sku IN ('sku1', 'sku2', 'sku3',..., 'sku60000'). I'm not sure of all
>> of the details of how I'll get from my single delimited string to that SQL
>> query, but I assume I can do it.
>> 2) Loop through the list of 1 to 60,000 products and issue 1 to 60,000
>> calls to a SQL stored procedure that returns data for a single product. My
>> client that's issuing the requests is multi-threaded and I'm comfortable
>> that I can set this up so that I'm issuing the 60,000 calls 5-10 or more
>> calls at a time.
>> The client that's requesting the 1 to 60,000 products is not very time
>> sensitive. It's a batch product feed process that runs 1 to 4 times a day.
>> The database I'm querying also supports a web site that does have
>> real-time requirements, so I don't want to structure my query in such a
>> way that my web service is negatively impacting the performance of my web
>> site.
>> I'm trying to understand the pros and cons of the two approaches and would
>> appreciate any inputs. Thoughts I have so far:
>> Option 1 may perform better for the product feed client because it has one
>> large network transaction instead of 1 to 60,000 small network
>> transactions. But Option 1 may put a more intense load on the SQL server
>> for a period of time, potentially negatively impacting the real-time
>> performance of the web site.
>> With either solution I probably want to look at ways to ensure that the
>> request for data for 1 to 60,000 products is done at a lower priority than
>> real-time requests from the web site.
>> Any thoughts or suggestions?
>> Thanks,
>> Zoe
>
Showing posts with label rows. Show all posts
Showing posts with label rows. Show all posts
Friday, March 30, 2012
Performance impact of different approaches to querying for 20,000 rows of data
I have two servers - a web server and a data server. I have a web service on
my web server that receives a request for information on anywhere from 1 to
60,000 products. I'm looking at a couple of different approaches for
querying the database for the product information on the 1 to 60,000
products.
1) Pass the product list into a SQL stored procedure as a single delimited
string and have that SQL stored procedure do a SELECT * FROM Products WHERE
Sku IN ('sku1', 'sku2', 'sku3',..., 'sku60000'). I'm not sure of all of the
details of how I'll get from my single delimited string to that SQL query,
but I assume I can do it.
2) Loop through the list of 1 to 60,000 products and issue 1 to 60,000 calls
to a SQL stored procedure that returns data for a single product. My client
that's issuing the requests is multi-threaded and I'm comfortable that I can
set this up so that I'm issuing the 60,000 calls 5-10 or more calls at a
time.
The client that's requesting the 1 to 60,000 products is not very time
sensitive. It's a batch product feed process that runs 1 to 4 times a day.
The database I'm querying also supports a web site that does have real-time
requirements, so I don't want to structure my query in such a way that my
web service is negatively impacting the performance of my web site.
I'm trying to understand the pros and cons of the two approaches and would
appreciate any inputs. Thoughts I have so far:
Option 1 may perform better for the product feed client because it has one
large network transaction instead of 1 to 60,000 small network transactions.
But Option 1 may put a more intense load on the SQL server for a period of
time, potentially negatively impacting the real-time performance of the web
site.
With either solution I probably want to look at ways to ensure that the
request for data for 1 to 60,000 products is done at a lower priority than
real-time requests from the web site.
Any thoughts or suggestions?
Thanks,
Zoe
Zoe,
Have a look at this link to see how to pass in a delimited list and process
it appropriately. But I would probably create 2 or more stored procedures to
handle the different ranges of product requests so you can get a proper
query plan for each. For instance if you only had one product specified you
can easily do an index seek and get a good plan. But to retrieve 60K
products you may need to do a scan or Merge Join or even Hash Join. Due to
parameter sniffing if the first time the proc was run it had 50K products
you would get a plan for that many rows. But when you call it the next time
even with 1 product you will still get the same plan as before. So I would
have your app decide how may products there will be and call one of 2 or
more (depends on how many different query plans you may encounter) sps so
they each get their own plan. The case where they only lookup a single
product you can use a straight forward query with an =. More than 1 you need
to use dynamic sql or parse it into a table with a UDF.
http://www.sommarskog.se/arrays-in-sql.html
Andrew J. Kelly SQL MVP
Solid Quality Mentors
"Zoe Hart" <zoe.hart@.nospam.competitive.com> wrote in message
news:eNn1t%235UIHA.1480@.TK2MSFTNGP06.phx.gbl...
>I have two servers - a web server and a data server. I have a web service
>on my web server that receives a request for information on anywhere from 1
>to 60,000 products. I'm looking at a couple of different approaches for
>querying the database for the product information on the 1 to 60,000
>products.
> 1) Pass the product list into a SQL stored procedure as a single delimited
> string and have that SQL stored procedure do a SELECT * FROM Products
> WHERE Sku IN ('sku1', 'sku2', 'sku3',..., 'sku60000'). I'm not sure of all
> of the details of how I'll get from my single delimited string to that SQL
> query, but I assume I can do it.
> 2) Loop through the list of 1 to 60,000 products and issue 1 to 60,000
> calls to a SQL stored procedure that returns data for a single product. My
> client that's issuing the requests is multi-threaded and I'm comfortable
> that I can set this up so that I'm issuing the 60,000 calls 5-10 or more
> calls at a time.
> The client that's requesting the 1 to 60,000 products is not very time
> sensitive. It's a batch product feed process that runs 1 to 4 times a day.
> The database I'm querying also supports a web site that does have
> real-time requirements, so I don't want to structure my query in such a
> way that my web service is negatively impacting the performance of my web
> site.
> I'm trying to understand the pros and cons of the two approaches and would
> appreciate any inputs. Thoughts I have so far:
> Option 1 may perform better for the product feed client because it has one
> large network transaction instead of 1 to 60,000 small network
> transactions. But Option 1 may put a more intense load on the SQL server
> for a period of time, potentially negatively impacting the real-time
> performance of the web site.
> With either solution I probably want to look at ways to ensure that the
> request for data for 1 to 60,000 products is done at a lower priority than
> real-time requests from the web site.
> Any thoughts or suggestions?
> Thanks,
> Zoe
>
|||Zoe Hart (zoe.hart@.nospam.competitive.com) writes:
> 1) Pass the product list into a SQL stored procedure as a single
> delimited string and have that SQL stored procedure do a SELECT * FROM
> Products WHERE Sku IN ('sku1', 'sku2', 'sku3',..., 'sku60000'). I'm not
> sure of all of the details of how I'll get from my single delimited
> string to that SQL query, but I assume I can do it.
Whatever, don't do exactly this. The time it would take to compile
that statement is amazing, particularly if you are on SQL 2000.
But there are other alternatives, as I discuss in my article
http://www.sommarskog.se/arrays-in-sql.html.
Andrew made an important point about the need for different plans due
to the number of elements in the list. I think the best approach is to
unpack the list into a temp table, as this will cause a recompile
and the actual join is likely to use the best plan.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
|||Thanks for all the good input. Our rough plan at this point is to have a
stored procedure that accepts an XML input that contains the 1-n SKUs. That
stored procedure will create a temporary table that with columns for the SKU
and the other data we intend to look up. The stored procedure will write the
1-n SKUs to the temporary table and then use UPDATE FROM to join the
temporary table to one or more tables that contain the data we need and
update the columns in the temporary table. We'll then SELECT * from the
temporary table FOR XML to get our results. We can either go with that
result as is or map it to a format we like better in the code that calls the
stored proc.
Thanks again.
Zoe
"Zoe Hart" <zoe.hart@.nospam.competitive.com> wrote in message
news:eNn1t%235UIHA.1480@.TK2MSFTNGP06.phx.gbl...
>I have two servers - a web server and a data server. I have a web service
>on my web server that receives a request for information on anywhere from 1
>to 60,000 products. I'm looking at a couple of different approaches for
>querying the database for the product information on the 1 to 60,000
>products.
> 1) Pass the product list into a SQL stored procedure as a single delimited
> string and have that SQL stored procedure do a SELECT * FROM Products
> WHERE Sku IN ('sku1', 'sku2', 'sku3',..., 'sku60000'). I'm not sure of all
> of the details of how I'll get from my single delimited string to that SQL
> query, but I assume I can do it.
> 2) Loop through the list of 1 to 60,000 products and issue 1 to 60,000
> calls to a SQL stored procedure that returns data for a single product. My
> client that's issuing the requests is multi-threaded and I'm comfortable
> that I can set this up so that I'm issuing the 60,000 calls 5-10 or more
> calls at a time.
> The client that's requesting the 1 to 60,000 products is not very time
> sensitive. It's a batch product feed process that runs 1 to 4 times a day.
> The database I'm querying also supports a web site that does have
> real-time requirements, so I don't want to structure my query in such a
> way that my web service is negatively impacting the performance of my web
> site.
> I'm trying to understand the pros and cons of the two approaches and would
> appreciate any inputs. Thoughts I have so far:
> Option 1 may perform better for the product feed client because it has one
> large network transaction instead of 1 to 60,000 small network
> transactions. But Option 1 may put a more intense load on the SQL server
> for a period of time, potentially negatively impacting the real-time
> performance of the web site.
> With either solution I probably want to look at ways to ensure that the
> request for data for 1 to 60,000 products is done at a lower priority than
> real-time requests from the web site.
> Any thoughts or suggestions?
> Thanks,
> Zoe
>
|||Sounds like about the best available approach.
Otherwise, if the query list is really that long, you might upload a
flat file to the server and then import it with an SSIS package.
Note that the numbers, 20k, 60k, are very tiny numbers for data in
terms of modern SQL Server performance, they are just very large
numbers if you try to list them all as part of a SQL command!
Josh
On Tue, 15 Jan 2008 09:52:25 -0500, "Zoe Hart"
<zoe.hart@.nospam.competitive.com> wrote:
>Thanks for all the good input. Our rough plan at this point is to have a
>stored procedure that accepts an XML input that contains the 1-n SKUs. That
>stored procedure will create a temporary table that with columns for the SKU
>and the other data we intend to look up. The stored procedure will write the
>1-n SKUs to the temporary table and then use UPDATE FROM to join the
>temporary table to one or more tables that contain the data we need and
>update the columns in the temporary table. We'll then SELECT * from the
>temporary table FOR XML to get our results. We can either go with that
>result as is or map it to a format we like better in the code that calls the
>stored proc.
>Thanks again.
>Zoe
>"Zoe Hart" <zoe.hart@.nospam.competitive.com> wrote in message
>news:eNn1t%235UIHA.1480@.TK2MSFTNGP06.phx.gbl...
>
my web server that receives a request for information on anywhere from 1 to
60,000 products. I'm looking at a couple of different approaches for
querying the database for the product information on the 1 to 60,000
products.
1) Pass the product list into a SQL stored procedure as a single delimited
string and have that SQL stored procedure do a SELECT * FROM Products WHERE
Sku IN ('sku1', 'sku2', 'sku3',..., 'sku60000'). I'm not sure of all of the
details of how I'll get from my single delimited string to that SQL query,
but I assume I can do it.
2) Loop through the list of 1 to 60,000 products and issue 1 to 60,000 calls
to a SQL stored procedure that returns data for a single product. My client
that's issuing the requests is multi-threaded and I'm comfortable that I can
set this up so that I'm issuing the 60,000 calls 5-10 or more calls at a
time.
The client that's requesting the 1 to 60,000 products is not very time
sensitive. It's a batch product feed process that runs 1 to 4 times a day.
The database I'm querying also supports a web site that does have real-time
requirements, so I don't want to structure my query in such a way that my
web service is negatively impacting the performance of my web site.
I'm trying to understand the pros and cons of the two approaches and would
appreciate any inputs. Thoughts I have so far:
Option 1 may perform better for the product feed client because it has one
large network transaction instead of 1 to 60,000 small network transactions.
But Option 1 may put a more intense load on the SQL server for a period of
time, potentially negatively impacting the real-time performance of the web
site.
With either solution I probably want to look at ways to ensure that the
request for data for 1 to 60,000 products is done at a lower priority than
real-time requests from the web site.
Any thoughts or suggestions?
Thanks,
Zoe
Zoe,
Have a look at this link to see how to pass in a delimited list and process
it appropriately. But I would probably create 2 or more stored procedures to
handle the different ranges of product requests so you can get a proper
query plan for each. For instance if you only had one product specified you
can easily do an index seek and get a good plan. But to retrieve 60K
products you may need to do a scan or Merge Join or even Hash Join. Due to
parameter sniffing if the first time the proc was run it had 50K products
you would get a plan for that many rows. But when you call it the next time
even with 1 product you will still get the same plan as before. So I would
have your app decide how may products there will be and call one of 2 or
more (depends on how many different query plans you may encounter) sps so
they each get their own plan. The case where they only lookup a single
product you can use a straight forward query with an =. More than 1 you need
to use dynamic sql or parse it into a table with a UDF.
http://www.sommarskog.se/arrays-in-sql.html
Andrew J. Kelly SQL MVP
Solid Quality Mentors
"Zoe Hart" <zoe.hart@.nospam.competitive.com> wrote in message
news:eNn1t%235UIHA.1480@.TK2MSFTNGP06.phx.gbl...
>I have two servers - a web server and a data server. I have a web service
>on my web server that receives a request for information on anywhere from 1
>to 60,000 products. I'm looking at a couple of different approaches for
>querying the database for the product information on the 1 to 60,000
>products.
> 1) Pass the product list into a SQL stored procedure as a single delimited
> string and have that SQL stored procedure do a SELECT * FROM Products
> WHERE Sku IN ('sku1', 'sku2', 'sku3',..., 'sku60000'). I'm not sure of all
> of the details of how I'll get from my single delimited string to that SQL
> query, but I assume I can do it.
> 2) Loop through the list of 1 to 60,000 products and issue 1 to 60,000
> calls to a SQL stored procedure that returns data for a single product. My
> client that's issuing the requests is multi-threaded and I'm comfortable
> that I can set this up so that I'm issuing the 60,000 calls 5-10 or more
> calls at a time.
> The client that's requesting the 1 to 60,000 products is not very time
> sensitive. It's a batch product feed process that runs 1 to 4 times a day.
> The database I'm querying also supports a web site that does have
> real-time requirements, so I don't want to structure my query in such a
> way that my web service is negatively impacting the performance of my web
> site.
> I'm trying to understand the pros and cons of the two approaches and would
> appreciate any inputs. Thoughts I have so far:
> Option 1 may perform better for the product feed client because it has one
> large network transaction instead of 1 to 60,000 small network
> transactions. But Option 1 may put a more intense load on the SQL server
> for a period of time, potentially negatively impacting the real-time
> performance of the web site.
> With either solution I probably want to look at ways to ensure that the
> request for data for 1 to 60,000 products is done at a lower priority than
> real-time requests from the web site.
> Any thoughts or suggestions?
> Thanks,
> Zoe
>
|||Zoe Hart (zoe.hart@.nospam.competitive.com) writes:
> 1) Pass the product list into a SQL stored procedure as a single
> delimited string and have that SQL stored procedure do a SELECT * FROM
> Products WHERE Sku IN ('sku1', 'sku2', 'sku3',..., 'sku60000'). I'm not
> sure of all of the details of how I'll get from my single delimited
> string to that SQL query, but I assume I can do it.
Whatever, don't do exactly this. The time it would take to compile
that statement is amazing, particularly if you are on SQL 2000.
But there are other alternatives, as I discuss in my article
http://www.sommarskog.se/arrays-in-sql.html.
Andrew made an important point about the need for different plans due
to the number of elements in the list. I think the best approach is to
unpack the list into a temp table, as this will cause a recompile
and the actual join is likely to use the best plan.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
|||Thanks for all the good input. Our rough plan at this point is to have a
stored procedure that accepts an XML input that contains the 1-n SKUs. That
stored procedure will create a temporary table that with columns for the SKU
and the other data we intend to look up. The stored procedure will write the
1-n SKUs to the temporary table and then use UPDATE FROM to join the
temporary table to one or more tables that contain the data we need and
update the columns in the temporary table. We'll then SELECT * from the
temporary table FOR XML to get our results. We can either go with that
result as is or map it to a format we like better in the code that calls the
stored proc.
Thanks again.
Zoe
"Zoe Hart" <zoe.hart@.nospam.competitive.com> wrote in message
news:eNn1t%235UIHA.1480@.TK2MSFTNGP06.phx.gbl...
>I have two servers - a web server and a data server. I have a web service
>on my web server that receives a request for information on anywhere from 1
>to 60,000 products. I'm looking at a couple of different approaches for
>querying the database for the product information on the 1 to 60,000
>products.
> 1) Pass the product list into a SQL stored procedure as a single delimited
> string and have that SQL stored procedure do a SELECT * FROM Products
> WHERE Sku IN ('sku1', 'sku2', 'sku3',..., 'sku60000'). I'm not sure of all
> of the details of how I'll get from my single delimited string to that SQL
> query, but I assume I can do it.
> 2) Loop through the list of 1 to 60,000 products and issue 1 to 60,000
> calls to a SQL stored procedure that returns data for a single product. My
> client that's issuing the requests is multi-threaded and I'm comfortable
> that I can set this up so that I'm issuing the 60,000 calls 5-10 or more
> calls at a time.
> The client that's requesting the 1 to 60,000 products is not very time
> sensitive. It's a batch product feed process that runs 1 to 4 times a day.
> The database I'm querying also supports a web site that does have
> real-time requirements, so I don't want to structure my query in such a
> way that my web service is negatively impacting the performance of my web
> site.
> I'm trying to understand the pros and cons of the two approaches and would
> appreciate any inputs. Thoughts I have so far:
> Option 1 may perform better for the product feed client because it has one
> large network transaction instead of 1 to 60,000 small network
> transactions. But Option 1 may put a more intense load on the SQL server
> for a period of time, potentially negatively impacting the real-time
> performance of the web site.
> With either solution I probably want to look at ways to ensure that the
> request for data for 1 to 60,000 products is done at a lower priority than
> real-time requests from the web site.
> Any thoughts or suggestions?
> Thanks,
> Zoe
>
|||Sounds like about the best available approach.
Otherwise, if the query list is really that long, you might upload a
flat file to the server and then import it with an SSIS package.
Note that the numbers, 20k, 60k, are very tiny numbers for data in
terms of modern SQL Server performance, they are just very large
numbers if you try to list them all as part of a SQL command!
Josh
On Tue, 15 Jan 2008 09:52:25 -0500, "Zoe Hart"
<zoe.hart@.nospam.competitive.com> wrote:
>Thanks for all the good input. Our rough plan at this point is to have a
>stored procedure that accepts an XML input that contains the 1-n SKUs. That
>stored procedure will create a temporary table that with columns for the SKU
>and the other data we intend to look up. The stored procedure will write the
>1-n SKUs to the temporary table and then use UPDATE FROM to join the
>temporary table to one or more tables that contain the data we need and
>update the columns in the temporary table. We'll then SELECT * from the
>temporary table FOR XML to get our results. We can either go with that
>result as is or map it to a format we like better in the code that calls the
>stored proc.
>Thanks again.
>Zoe
>"Zoe Hart" <zoe.hart@.nospam.competitive.com> wrote in message
>news:eNn1t%235UIHA.1480@.TK2MSFTNGP06.phx.gbl...
>
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)