need suggestions for indexing

I have a table that contains fields such as:
CustomerID ProductName SmallDateTime AnotherID1 AnotherID2
Amount
The data comes from another database that throws data over to this table,
and so no field in the above is a foreign key or primary key.
The ID fields are NOT going to be unique, even when combined with the
smalldatetime field.
The table contains millions of records and my stored proc needs to execute
as fast as possible. This is a mission-critical and speed-sensitive
environment.
MY QUESTION: What index(es) should I use to optimize this proc...
Create Procedure as GetCustomerProducts
@.CustomerID int,
@.ProductName varchar(10),
as
Select [field names here]
from [the-above-shown-table]
where CustomerID = @.CustomerID
and ProductName = @.ProductName
Order by ProductName, AnotherID1, AnotherID2
go
The SP will return anywhere from 1 to 100,000 records, depending upon the
two input params. I need the results "instantly". Hardware is top-notch.
Index suggestions?
I would start with a clustered index on CustomerID. Try the query and
measure the results then add a nonclustered on ProductName and see if it
speeds up or slows down. Alternatively, feed the query to the index tuning
wizard and experiment with the results.
Walter
"HK" <replywithingroup@.notreal.com> wrote in message
news:let9f.3242$Hs.3087@.tornado.socal.rr.com...
>I have a table that contains fields such as:
> ----
> --
> CustomerID ProductName SmallDateTime AnotherID1 AnotherID2
> Amount
> ----
> --
> The data comes from another database that throws data over to this table,
> and so no field in the above is a foreign key or primary key.
> The ID fields are NOT going to be unique, even when combined with the
> smalldatetime field.
> The table contains millions of records and my stored proc needs to execute
> as fast as possible. This is a mission-critical and speed-sensitive
> environment.
> MY QUESTION: What index(es) should I use to optimize this proc...
> Create Procedure as GetCustomerProducts
> @.CustomerID int,
> @.ProductName varchar(10),
> as
> Select [field names here]
> from [the-above-shown-table]
> where CustomerID = @.CustomerID
> and ProductName = @.ProductName
> Order by ProductName, AnotherID1, AnotherID2
> go
>
> The SP will return anywhere from 1 to 100,000 records, depending upon the
> two input params. I need the results "instantly". Hardware is
> top-notch.
> Index suggestions?
>
>
|||depending on how you load the data and how many changes
how about this...
you could make one big composite clustered with all the columns in order of
most restrictive(unique) to least
and make a non clustered on CustomerID and Product name in same order
"Walt Mallon" <waltmallon@.hotmail.nospam.com> wrote in message
news:uzX6yal3FHA.3876@.TK2MSFTNGP09.phx.gbl...
>I would start with a clustered index on CustomerID. Try the query and
>measure the results then add a nonclustered on ProductName and see if it
>speeds up or slows down. Alternatively, feed the query to the index tuning
>wizard and experiment with the results.
> Walter
>
> "HK" <replywithingroup@.notreal.com> wrote in message
> news:let9f.3242$Hs.3087@.tornado.socal.rr.com...
>
|||HK wrote:
> I have a table that contains fields such as:
> ----
> --
> CustomerID ProductName SmallDateTime AnotherID1
> AnotherID2 Amount
> ----
> --
> The data comes from another database that throws data over to this
> table, and so no field in the above is a foreign key or primary key.
> The ID fields are NOT going to be unique, even when combined with the
> smalldatetime field.
> The table contains millions of records and my stored proc needs to
> execute as fast as possible. This is a mission-critical and
> speed-sensitive environment.
> MY QUESTION: What index(es) should I use to optimize this proc...
> Create Procedure as GetCustomerProducts
> @.CustomerID int,
> @.ProductName varchar(10),
> as
> Select [field names here]
> from [the-above-shown-table]
> where CustomerID = @.CustomerID
> and ProductName = @.ProductName
> Order by ProductName, AnotherID1, AnotherID2
> go
>
> The SP will return anywhere from 1 to 100,000 records, depending upon
> the two input params. I need the results "instantly". Hardware is
> top-notch.
> Index suggestions?
I would add an identity column to the table and create a unique
clustered index on CustomerID + ProductName + ID. That way, you can
remove rows. It's no elegant (but you have no keys and that's already
not so elegant), but it does prevent you from having to duplicate any
clustered index keys in the non-clustered indexes and prevents SQL
Server from adding unique identifiers to all non-duplicate rows in a
non-unique clustered index.
The Order By clause confuses me. Why do you need to sort on the
ProductName column when you are querying for a single ProductName?
I would eliminate the ORDER BY if possible. Most queries don't require
sorting on SQL Server as the app can handle this. If you must have
sorting, then I would remove the ProductName and do two things:
1- Check performance of the queries that return the small, medium, and
larger result sets. If the Sort operation is not consuming too much
overhead for the larger result sets, then leave the indexing as is.
2- If the Sort operation is consuming too much overhead, add the
AnotherID1 + AnotherID2 to the clustered index before the ID column.
Also, you have not provided all the necessary detail for the query. You
left out the column names, which are important to know when designing
queries. For example, there's generally littlle reason to request a
column you are filtering against since you already know the answer.
Also, if you can design covering indexes, you can speed up queries by
preventing bookmark lookup operations. Not the case here as we only have
a single unique, clustered index.
David Gugick
Quest Software
www.imceda.com
www.quest.com
sql

need suggestions for indexing

I have a table that contains fields such as:
----
--
CustomerID ProductName SmallDateTime AnotherID1 AnotherID2
Amount
----
--
The data comes from another database that throws data over to this table,
and so no field in the above is a foreign key or primary key.
The ID fields are NOT going to be unique, even when combined with the
smalldatetime field.
The table contains millions of records and my stored proc needs to execute
as fast as possible. This is a mission-critical and speed-sensitive
environment.
MY QUESTION: What index(es) should I use to optimize this proc...
Create Procedure as GetCustomerProducts
@.CustomerID int,
@.ProductName varchar(10),
as
Select [field names here]
from [the-above-shown-table]
where CustomerID = @.CustomerID
and ProductName = @.ProductName
Order by ProductName, AnotherID1, AnotherID2
go
The SP will return anywhere from 1 to 100,000 records, depending upon the
two input params. I need the results "instantly". Hardware is top-notch.
Index suggestions?I would start with a clustered index on CustomerID. Try the query and
measure the results then add a nonclustered on ProductName and see if it
speeds up or slows down. Alternatively, feed the query to the index tuning
wizard and experiment with the results.
Walter
"HK" <replywithingroup@.notreal.com> wrote in message
news:let9f.3242$Hs.3087@.tornado.socal.rr.com...
>I have a table that contains fields such as:
> ----
> --
> CustomerID ProductName SmallDateTime AnotherID1 AnotherID2
> Amount
> ----
> --
> The data comes from another database that throws data over to this table,
> and so no field in the above is a foreign key or primary key.
> The ID fields are NOT going to be unique, even when combined with the
> smalldatetime field.
> The table contains millions of records and my stored proc needs to execute
> as fast as possible. This is a mission-critical and speed-sensitive
> environment.
> MY QUESTION: What index(es) should I use to optimize this proc...
> Create Procedure as GetCustomerProducts
> @.CustomerID int,
> @.ProductName varchar(10),
> as
> Select [field names here]
> from [the-above-shown-table]
> where CustomerID = @.CustomerID
> and ProductName = @.ProductName
> Order by ProductName, AnotherID1, AnotherID2
> go
>
> The SP will return anywhere from 1 to 100,000 records, depending upon the
> two input params. I need the results "instantly". Hardware is
> top-notch.
> Index suggestions?
>
>|||depending on how you load the data and how many changes
how about this...
you could make one big composite clustered with all the columns in order of
most restrictive(unique) to least
and make a non clustered on CustomerID and Product name in same order
"Walt Mallon" <waltmallon@.hotmail.nospam.com> wrote in message
news:uzX6yal3FHA.3876@.TK2MSFTNGP09.phx.gbl...
>I would start with a clustered index on CustomerID. Try the query and
>measure the results then add a nonclustered on ProductName and see if it
>speeds up or slows down. Alternatively, feed the query to the index tuning
>wizard and experiment with the results.
> Walter
>
> "HK" <replywithingroup@.notreal.com> wrote in message
> news:let9f.3242$Hs.3087@.tornado.socal.rr.com...
>>I have a table that contains fields such as:
>> ----
>> --
>> CustomerID ProductName SmallDateTime AnotherID1 AnotherID2
>> Amount
>> ----
>> --
>> The data comes from another database that throws data over to this table,
>> and so no field in the above is a foreign key or primary key.
>> The ID fields are NOT going to be unique, even when combined with the
>> smalldatetime field.
>> The table contains millions of records and my stored proc needs to
>> execute
>> as fast as possible. This is a mission-critical and speed-sensitive
>> environment.
>> MY QUESTION: What index(es) should I use to optimize this proc...
>> Create Procedure as GetCustomerProducts
>> @.CustomerID int,
>> @.ProductName varchar(10),
>> as
>> Select [field names here]
>> from [the-above-shown-table]
>> where CustomerID = @.CustomerID
>> and ProductName = @.ProductName
>> Order by ProductName, AnotherID1, AnotherID2
>> go
>>
>> The SP will return anywhere from 1 to 100,000 records, depending upon the
>> two input params. I need the results "instantly". Hardware is
>> top-notch.
>> Index suggestions?
>>
>>
>|||HK wrote:
> I have a table that contains fields such as:
> ----
> --
> CustomerID ProductName SmallDateTime AnotherID1
> AnotherID2 Amount
> ----
> --
> The data comes from another database that throws data over to this
> table, and so no field in the above is a foreign key or primary key.
> The ID fields are NOT going to be unique, even when combined with the
> smalldatetime field.
> The table contains millions of records and my stored proc needs to
> execute as fast as possible. This is a mission-critical and
> speed-sensitive environment.
> MY QUESTION: What index(es) should I use to optimize this proc...
> Create Procedure as GetCustomerProducts
> @.CustomerID int,
> @.ProductName varchar(10),
> as
> Select [field names here]
> from [the-above-shown-table]
> where CustomerID = @.CustomerID
> and ProductName = @.ProductName
> Order by ProductName, AnotherID1, AnotherID2
> go
>
> The SP will return anywhere from 1 to 100,000 records, depending upon
> the two input params. I need the results "instantly". Hardware is
> top-notch.
> Index suggestions?
I would add an identity column to the table and create a unique
clustered index on CustomerID + ProductName + ID. That way, you can
remove rows. It's no elegant (but you have no keys and that's already
not so elegant), but it does prevent you from having to duplicate any
clustered index keys in the non-clustered indexes and prevents SQL
Server from adding unique identifiers to all non-duplicate rows in a
non-unique clustered index.
The Order By clause confuses me. Why do you need to sort on the
ProductName column when you are querying for a single ProductName?
I would eliminate the ORDER BY if possible. Most queries don't require
sorting on SQL Server as the app can handle this. If you must have
sorting, then I would remove the ProductName and do two things:
1- Check performance of the queries that return the small, medium, and
larger result sets. If the Sort operation is not consuming too much
overhead for the larger result sets, then leave the indexing as is.
2- If the Sort operation is consuming too much overhead, add the
AnotherID1 + AnotherID2 to the clustered index before the ID column.
Also, you have not provided all the necessary detail for the query. You
left out the column names, which are important to know when designing
queries. For example, there's generally littlle reason to request a
column you are filtering against since you already know the answer.
Also, if you can design covering indexes, you can speed up queries by
preventing bookmark lookup operations. Not the case here as we only have
a single unique, clustered index.
--
David Gugick
Quest Software
www.imceda.com
www.quest.com

need suggestions for indexing

I have a table that contains fields such as:
----
--
CustomerID ProductName SmallDateTime AnotherID1 AnotherID2
Amount
----
--
The data comes from another database that throws data over to this table,
and so no field in the above is a foreign key or primary key.
The ID fields are NOT going to be unique, even when combined with the
smalldatetime field.
The table contains millions of records and my stored proc needs to execute
as fast as possible. This is a mission-critical and speed-sensitive
environment.
MY QUESTION: What index(es) should I use to optimize this proc...
Create Procedure as GetCustomerProducts
@.CustomerID int,
@.ProductName varchar(10),
as
Select [field names here]
from [the-above-shown-table]
where CustomerID = @.CustomerID
and ProductName = @.ProductName
Order by ProductName, AnotherID1, AnotherID2
go
The SP will return anywhere from 1 to 100,000 records, depending upon the
two input params. I need the results "instantly". hardware is top-notch.
Index suggestions?I would start with a clustered index on CustomerID. Try the query and
measure the results then add a nonclustered on ProductName and see if it
speeds up or slows down. Alternatively, feed the query to the index tuning
wizard and experiment with the results.
Walter
"HK" <replywithingroup@.notreal.com> wrote in message
news:let9f.3242$Hs.3087@.tornado.socal.rr.com...
>I have a table that contains fields such as:
> ----
--
> --
> CustomerID ProductName SmallDateTime AnotherID1 AnotherID2
> Amount
> ----
--
> --
> The data comes from another database that throws data over to this table,
> and so no field in the above is a foreign key or primary key.
> The ID fields are NOT going to be unique, even when combined with the
> smalldatetime field.
> The table contains millions of records and my stored proc needs to execute
> as fast as possible. This is a mission-critical and speed-sensitive
> environment.
> MY QUESTION: What index(es) should I use to optimize this proc...
> Create Procedure as GetCustomerProducts
> @.CustomerID int,
> @.ProductName varchar(10),
> as
> Select [field names here]
> from [the-above-shown-table]
> where CustomerID = @.CustomerID
> and ProductName = @.ProductName
> Order by ProductName, AnotherID1, AnotherID2
> go
>
> The SP will return anywhere from 1 to 100,000 records, depending upon the
> two input params. I need the results "instantly". hardware is
> top-notch.
> Index suggestions?
>
>|||depending on how you load the data and how many changes
how about this...
you could make one big composite clustered with all the columns in order of
most restrictive(unique) to least
and make a non clustered on CustomerID and Product name in same order
"Walt Mallon" <waltmallon@.hotmail.nospam.com> wrote in message
news:uzX6yal3FHA.3876@.TK2MSFTNGP09.phx.gbl...
>I would start with a clustered index on CustomerID. Try the query and
>measure the results then add a nonclustered on ProductName and see if it
>speeds up or slows down. Alternatively, feed the query to the index tuning
>wizard and experiment with the results.
> Walter
>
> "HK" <replywithingroup@.notreal.com> wrote in message
> news:let9f.3242$Hs.3087@.tornado.socal.rr.com...
>|||HK wrote:
> I have a table that contains fields such as:
> ----
--
> --
> CustomerID ProductName SmallDateTime AnotherID1
> AnotherID2 Amount
> ----
--
> --
> The data comes from another database that throws data over to this
> table, and so no field in the above is a foreign key or primary key.
> The ID fields are NOT going to be unique, even when combined with the
> smalldatetime field.
> The table contains millions of records and my stored proc needs to
> execute as fast as possible. This is a mission-critical and
> speed-sensitive environment.
> MY QUESTION: What index(es) should I use to optimize this proc...
> Create Procedure as GetCustomerProducts
> @.CustomerID int,
> @.ProductName varchar(10),
> as
> Select [field names here]
> from [the-above-shown-table]
> where CustomerID = @.CustomerID
> and ProductName = @.ProductName
> Order by ProductName, AnotherID1, AnotherID2
> go
>
> The SP will return anywhere from 1 to 100,000 records, depending upon
> the two input params. I need the results "instantly". hardware is
> top-notch.
> Index suggestions?
I would add an identity column to the table and create a unique
clustered index on CustomerID + ProductName + ID. That way, you can
remove rows. It's no elegant (but you have no keys and that's already
not so elegant), but it does prevent you from having to duplicate any
clustered index keys in the non-clustered indexes and prevents SQL
Server from adding unique identifiers to all non-duplicate rows in a
non-unique clustered index.
The Order By clause confuses me. Why do you need to sort on the
ProductName column when you are querying for a single ProductName?
I would eliminate the ORDER BY if possible. Most queries don't require
sorting on SQL Server as the app can handle this. If you must have
sorting, then I would remove the ProductName and do two things:
1- Check performance of the queries that return the small, medium, and
larger result sets. If the Sort operation is not consuming too much
overhead for the larger result sets, then leave the indexing as is.
2- If the Sort operation is consuming too much overhead, add the
AnotherID1 + AnotherID2 to the clustered index before the ID column.
Also, you have not provided all the necessary detail for the query. You
left out the column names, which are important to know when designing
queries. For example, there's generally littlle reason to request a
column you are filtering against since you already know the answer.
Also, if you can design covering indexes, you can speed up queries by
preventing bookmark lookup operations. Not the case here as we only have
a single unique, clustered index.
David Gugick
Quest Software
www.imceda.com
www.quest.com

Need SQL statement help!

Hello everyone,
I need help with two SQL statements
The first is:
I have a table called tbSitters which contains two columns named
bsLastName and bsAboutMe. I want to be able to find records where the
value stored in bsLastName can be found in bsAboutMe for a single row
(not all records) bsAboutMe is a text field which may contain up to 50
words or more.
The second is:
I have a table called tbPcodes which has two columns named bsID and
pcMyZips. I want to be able to bring up any duplicates in pcMyZips,
which are associated to bsID. For example:
bsID pcMyZips
001 100
001 100
001 101
001 102
002 201
002 202
002 203
002 204
It would return bsID 001 because there is a duplicate entry(100) in
pcMyZips.
Any help would be greatly appreciated!
Thanks Robert> The first is:
> I have a table called tbSitters which contains two columns named
> bsLastName and bsAboutMe. I want to be able to find records where the
> value stored in bsLastName can be found in bsAboutMe for a single row
> (not all records) bsAboutMe is a text field which may contain up to 50
> words or more.

> The second is:
> I have a table called tbPcodes which has two columns named bsID and
> pcMyZips. I want to be able to bring up any duplicates in pcMyZips,
> which are associated to bsID. For example:
> bsID pcMyZips
> 001 100
> 001 100
> 001 101
> 001 102
> 002 201
> 002 202
> 002 203
> 002 204
>
For the first one try:
SELECT * FROM tbSitters
WHERE bsAboutMe LIKE '%' + bsLastName + '%'
For the second one try:
SELECT bsID, pcMyZips, COUNT(pcMyZips)
FROM pcMyZips
GROUP BY bsID, pcMyZips
HAVING COUNT(pcMyZips) > 1
Rick Sawtell|||Hi Rick,
Thans for the reply!
The second statement works perfect.
The problems I'm having with the results of the first statement, is
some members only enter in their intial in the bsLastName field, so a
lot of results are being returned. Would it be possible to specify that
there must be a blank space before and after when matching the text
values? This would eliminate a lot of the false returns.
Or, If that's not possible, could the statement be altered to include
bsLastName('s) which are a minimum of 3 characters long only?
Much appreciated Rick,
Thanks, Rob|||ROBinBRAMPTON wrote:

> Would it be possible to specify
> that there must be a blank space before and after when matching the
> text values? This would eliminate a lot of the false returns.
Try SELECT * FROM tbSitters
WHERE bsAboutMe LIKE '% ' + bsLastName + ' %'
HTH,
Stijn Verrept.

Need some suggestion

I have a stored procedure to retrieve records from a database, each record contains the following fields: merchant_id, shop_id, shop_sales_amount

each merchant can have several shops and the corresponding shop_sales_amount

the format of the report(detail section) is like this:

merchant1 $10000
merchant1-shop1 $5000
merchant1-shop2 $5000
merchant2 $1000
merchant2-shop1 $500
merchant2-shop2 $500
merchant3 $100
merchant3-shop1 $50
merchant3-shop2 $50
.....

merchant1 is group 1, merchant 2 is group 2, merchant 3 is group 3...etc, the amount following the merchant name is the total sales amount of each shop under the merchant, the ordering of the group is in descending order of this total sales amount...

It is quite complicated, seriously need some suggestions or hints, I am quite new to crystal reports. Hope someone could help me out, thanksSo you should create two Crystal report groups, Merchant (#1) and Shop (#2), an order on descending sales amount beneath these (using the record sort expert). Insert the sum of the sales amount grouped at the shop level (Insert Summary, field to summarize: shop_sales_amount; calculate this summary: Sum; summary location: group 2) and move from the group2 footer to the header.

Viola! (if I understand you correctly.)sql

Need some help with check contraint in SQL2005

Hi,

I have this table which contains 2 datetime fields called Startdate & enddate

Startdate cannot contain NULL
Enddate can because it can be a single day event or a period.

If enddate is not null it must be later than startdate
i created this checkconstraint

enddate > startdate

But i also fires when enddate = NULL

Someone knows howto do this?

Cheers Wimenddate > startdate
OR
enddate IS NULL|||Thanx for your reply.

But enddate could be NULL when NOTNULL it should be later then Startdate|||Your point being? :)|||Sorry my bad, calendar control gives me 1-1-1980 instead of NUll

Thanx for ur help!!
CHeers Wimsql