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 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

Need some help in searching

Dear ASP.NET

How can I find records that contain a STRING from some (more than one) other fields ?

for example, I have:

Name_First = "aaa"
Name_Middle = "bbb"
Name_Last = "ccc"

Key_Words = "aaa,bbb,ccc"
(includes all values - comma separated)

How can I do the SELECT so that when I search for "bbb" on Key_Words I will get my record ?

Should I use "LIKE %aaa%" or something like this ?
(should I keep the comma separators ?)

Thanks in advance, Yovav.You can use like % for sure. Generally when I have searching functions where a user can enter one or more fields

I apppend each field in the SQL string as I build it. So for example mine may look something like the following:

DECLARE @.SQLWHERE varchar(1000)
SET @.SQLWHERE = ''

IF @.FirstName IS NOT NULL
SELECT @.SQLWHERE = ' WHERE FirstName ' + ' LIKE ''' + '%' + @.FirstName + '%'''

IF @.LastName IS NOT NULL
BEGIN
IF @.SQLWHERE = ''
SELECT @.SQLWHERE = @.SQLWHERE + ' WHERE P.LastName ' + ' LIKE ''' + '%' +
@.LastName + '%'''
ELSE
SELECT @.SQLWHERE = @.SQLWHERE + ' AND P.LastName ' + ' LIKE ''' + '%' + @.LastName + '%'''
END

And you can keep doing that type of logic for as many as you would like,

Another option is not to include any "%" at all and then indicate on your textboxes where users search that they can enter a * or % for like searches.

Just food for thought.|||I doing this search on 6 NTEXT fields...
so I thought maybe it will be faster if I do it on one field that has the content of the all 6...

what do U think ?

+
How should I write it ?
"SELECT * FROM X WHERE Key_Words LIKE %'" & SearchString & "'%" ?|||' SELECT * FROM X WHERE FirstName ' + ' LIKE ''' + '%' + @.FirstName + '%'''
.LastName ' + ' LIKE ''' + '%' + @.LastName + '%'''

ANd keep appending for each additional field until you have all six included.

If this is a stored procedure assign your string to variable and then at the end
call exec yourVarialbename

For example

EXEC (@.SQLStatement)

need query...its urgent

hi friends,

I am facing one problem...
I am using ms-access .
I have parent table with fields (empid(primary key),password,flag).and child table with fields (empid(fk),empname,date,intime,outime,remarks).

If i enter empid and name of month,the output contains fields like:empid,empname,date,intime,outime,remarks.

My problem is if i enter empid and name of month,it has to get all records of employee(holding that empid) throughout the specified month..

Please send solution for this...its very urgent.......Hi..
Send me your query for getting records
by
ramesh

need help-how to call a function by date in SQL server

I am writing a project in .NET.
I want to check every minute two fields in the data base:
time and start date
time end end date
if in the check the date and time arrives I want to call a function automaticly.
what is the best way to do it in windows service?
maybe directly from the database?
I would like to get sugestions to do it
thanksWell, you can fire a trigger in the db when a new record is added to the table. In that trigger you could shell out to run a .NET app that fires an event that the Windows service is watching for. Or you could poll the db from the Windows service every few seconds to see if there is new data.

Any of these work for you?

Don|||Well, you could also just add a job to the database that starts a stored procedure every x minutes.

In this particular case, the sp_add_job system procedure is agood place to start.