Composite index design question

I have converted a database to support multiple clients by adding a ClientID
(int) to most of the 200 tables. This is to enable an application to use a
single database for multiple clients, separating the data via the ClientID
Where the primary key was OrderID, or ProductID, it is now ( ClientID,
OrderID) and (ClientID, ProductID) respectively.
The majority of clustered indexes are composite and have ClientID as the
first column. This of course means most of the foreign key indexes have
ClientID as the first column in the index. I even added ClientID to most
other nonclustered indexes since I know that ClientID will be in every join
and specified in the WHERE clause of every query.
It occurred to me recently that this database may have hundreds of thousands
of Orders and Products, but will likely only have 30-50 unique values of
ClientID.
This being the case, I would assume I will get better performance if the
indexes were rearranged to be ( ProductID, ClientID) and ( OrderID,
ClientID) ? The database tables are not sufficiently large at this point to
make a difference, but with 30 clients they will hit the 100K mark in a shor
t
period of time.Hi
When you create a composite index on columns SQL Server keeps statistics on
only one (first) column. So make sure that this column is selective enough.
http://www.sql-server-performance.c...ite_indexes.asp
"mikenz" <mikewnz@.newsgroups.nospam> wrote in message
news:BEA8FDD5-CD9C-4EA6-9942-DD6D4D2A6331@.microsoft.com...
>I have converted a database to support multiple clients by adding a
>ClientID
> (int) to most of the 200 tables. This is to enable an application to use
> a
> single database for multiple clients, separating the data via the ClientID
> Where the primary key was OrderID, or ProductID, it is now ( ClientID,
> OrderID) and (ClientID, ProductID) respectively.
> The majority of clustered indexes are composite and have ClientID as the
> first column. This of course means most of the foreign key indexes have
> ClientID as the first column in the index. I even added ClientID to most
> other nonclustered indexes since I know that ClientID will be in every
> join
> and specified in the WHERE clause of every query.
> It occurred to me recently that this database may have hundreds of
> thousands
> of Orders and Products, but will likely only have 30-50 unique values of
> ClientID.
> This being the case, I would assume I will get better performance if the
> indexes were rearranged to be ( ProductID, ClientID) and ( OrderID,
> ClientID) ? The database tables are not sufficiently large at this point
> to
> make a difference, but with 30 clients they will hit the 100K mark in a
> short
> period of time.
>|||Consider seperate non-composite indexes for ClientID and ProductID, since
this will provide more flexibility when generating an execution plan. Also,
consider not clustering on ClientID. With 50 unique clients using the
database, clustering will result in additional I/O and page fragmentation.
I generally avoid using composite columns except on clustered indexes, and I
generally avoid clustered indexes except on data warehouse tables where
there is a large amount of fairly static data that is frequently grouped or
sorted in mass for reporting purposes. There is little advantage to
clustering indexes in an OLTP database, unless it is on a bookmarked id
column like Product.ProductID.
"mikenz" <mikewnz@.newsgroups.nospam> wrote in message
news:BEA8FDD5-CD9C-4EA6-9942-DD6D4D2A6331@.microsoft.com...
>I have converted a database to support multiple clients by adding a
>ClientID
> (int) to most of the 200 tables. This is to enable an application to use
> a
> single database for multiple clients, separating the data via the ClientID
> Where the primary key was OrderID, or ProductID, it is now ( ClientID,
> OrderID) and (ClientID, ProductID) respectively.
> The majority of clustered indexes are composite and have ClientID as the
> first column. This of course means most of the foreign key indexes have
> ClientID as the first column in the index. I even added ClientID to most
> other nonclustered indexes since I know that ClientID will be in every
> join
> and specified in the WHERE clause of every query.
> It occurred to me recently that this database may have hundreds of
> thousands
> of Orders and Products, but will likely only have 30-50 unique values of
> ClientID.
> This being the case, I would assume I will get better performance if the
> indexes were rearranged to be ( ProductID, ClientID) and ( OrderID,
> ClientID) ? The database tables are not sufficiently large at this point
> to
> make a difference, but with 30 clients they will hit the 100K mark in a
> short
> period of time.
>

complex query problem

clientid employeeid
customers <---- jobcards ------> employees
| handedovertoid
__________________________|_______________________ | | |
jobcarddetails-->employees jobcardlabor-->employees jobcardparts-->parts

hi Guys,
above is the structure of the transaction tables:
jobcards, jobcarddetails,jobcardlabor,jobcardparts
where
customer,employees and parts are the master reference tables.
I want to retrieve the records from these tables in a single recordset, so i avoid roundtrips, the sql query is below. Problem with this query is that i want it to return the maximum rows from each of the child table, so i can iterate and fill through a single recordset. Problem area is the employee table which i referred by jobcards, jobcarddetails and jobcardlabor.
example : if 2 records in jobcarddetails then it return 2 rows with duplicating record information for jobcard master and other tables. but if there are 2 records in jobcardlabor then i want it to return 2 records in all with jobcard details and jobcardlabor having different record data.
I hope my requirment is clear. now if some1 can solve this query or just suggest an alternative.

SELECT JobCards.JobCardID, JobCards.CustomerID,
JobCards.EmployeeID, JobCards.PersonReporting,
employees.firstname + ' ' + employees.lastname as reportTakenbyName,
JobCards.NatureOfProblem, JobCards.HandedToID,
e1.firstname + ' ' + e1.lastname as HandedToName,
JobCards.InvoiceNumber, JobCards.DateReceived,
JobCards.DateRequired, JobCards.MakeAndModel,
JobCards.SerialNumber, JobCards.SoftwareProblem,
JobCards.DateFinished, JobCards.DatePickedUp,
JobCards.SalesTaxRate, JobCards.JobSite,
JobCards.Accessories, Customers.CompanyName,
JobCardDetails.Description, jobCardDetails.ActionTaken,
JobCardDetails.DateTakenUp,
JobCardDetails.DateFinished AS detailsDtFinished,
JobCardLabor.EmployeeID AS EmployeeLabourID,
JobCardLabor.BillableHours, JobCardLabor.BillingRate,
JobCardLabor.Comment, JobCardLabor.JobCardLaborID,
e3.firstname + ' ' + e3.lastname as JobCardLaborName,
JobCardParts.UnitPrice, JobCardParts.Quantity,
Parts.PartName, JobCardParts.JobCardPartID,
JobCardDetails.AttendedBy,
e2.firstname + ' ' + e2.lastname as AttendedByName
FROM jobcards, jobcarddetails, jobcardlabor, jobcardparts, employees, employees e1,
employees e2, employees e3,customers, parts
where jobcards.jobcardid = jobcarddetails.jobcardid and
jobcards.jobcardid = jobcardlabor.jobcardid and
jobcards.jobcardid = jobcardparts.jobcardid and
jobcards.employeeid = employees.employeeid and
jobcards.handedtoid = e1.employeeid and
jobcarddetails.attendedby = e2.employeeid and
jobcardlabor.employeeid = e3.employeeid and
jobcards.customerid = customers.customerid and
jobcardparts.partid = parts.partidPost DDL, test data and desired resultset to speed up the process.|||hi,
have attached the ddl script, test data in text document(csv) per table and desired result in a jpg file as well(which i generated using msdatashape)

hope it helps.

thanks.|||Try this FROM clause

FROM JobCards
LEFT JOIN JobCardDetails ON JobCards.JobCardID = JobCardDetails.JobCardID
LEFT JOIN JobCardLabor ON JobCards.JobCardID = JobCardLabor.JobCardID
LEFT JOIN JobCardParts ON JobCards.JobCardID = JobCardParts.JobCardID
LEFT JOIN Employees ON JobCards.EmployeeID = Employees.EmployeeID
LEFT JOIN Employees e1 ON JobCards.HandedToID = e1.EmployeeID
FULL JOIN Employees e2 ON JobCardDetails.AttendedBy = e2.EmployeeID
FULL JOIN Employees e3 ON JobCardLabor.EmployeeID = e3.EmployeeID
LEFT JOIN customers ON JobCards.CustomerID = customers.customerid
FULL JOIN Parts ON JobCardParts.PartID = Parts.PartID

I am not sure what outer joins are necessary. You can optimize.|||Doesnt quite give me the desired result.

if u use dataenvironment and create a Hierarchial recordset then it will show u the results which i m looking for.

coz if u add a single record in lets say parts then it gives 3 records more: 1 for jobcarddetails and two for jobcardlabor.

the full outer joins r not serving any purpose as i tried those as well.|||MSSQLOLEDB data provider cannot return a hieraset.|||yes i knw abt msoledb provider, thats the reason y this problem.

but no solution yet except that i have to use that hierach recordset.