Is there such thing as cross-table index or something?

30 Jul 2005 8:43 AM

Rich

CREATE TABLE [SalesForecast] (
[SaleDate] [datetime] NOT NULL ,
[CustID] [varchar] (10) NOT NULL ,
[F1] [money] NOT NULL
) ON [PRIMARY]

CREATE TABLE [Sales] (
[SaleDate] [datetime] NOT NULL ,
[CustID] [varchar] (10) NOT NULL ,
[S1] [money] NOT NULL
) ON [PRIMARY]

CREATE NONCLUSTERED INDEX SalesForecast_CustID ON SalesForecast (CustID)
CREATE NONCLUSTERED INDEX Sales_CustID ON Sales (CustID)

CREATE NONCLUSTERED INDEX SalesForecast_SaleDate ON SalesForecast (SaleDate)
CREATE NONCLUSTERED INDEX Sales_SaleDate ON Sales (SaleDate)

When I mix a query with both tables like this its really slow:

SELECT A.S1 * B.F1
FROM Sales A, SalesForecast B
WHERE A.SaleDate = B.SaleDate
AND A.CustID = B.CustID
AND A.SaleDate > '20050101'

What am I doing wrong, this seems to be correct..

30 Jul 2005 9:06 AM

Narayana Vyas Kondreddi

It would help to have a clustered index on the date column. You don't look
like you have any clustered indexes at all, which is not a good practice.

--
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @ http://vyaskn.tripod.com/

"Rich" <no@spam.invalid> wrote in message

news:44HGe.53673$4o.23499@fed1read06...
Show quote

> CREATE TABLE [SalesForecast] (
> [SaleDate] [datetime] NOT NULL ,
> [CustID] [varchar] (10) NOT NULL ,
> [F1] [money] NOT NULL
> ) ON [PRIMARY]
>
> CREATE TABLE [Sales] (
> [SaleDate] [datetime] NOT NULL ,
> [CustID] [varchar] (10) NOT NULL ,
> [S1] [money] NOT NULL
> ) ON [PRIMARY]
>
>
> CREATE NONCLUSTERED INDEX SalesForecast_CustID ON SalesForecast (CustID)
> CREATE NONCLUSTERED INDEX Sales_CustID ON Sales (CustID)
>
> CREATE NONCLUSTERED INDEX SalesForecast_SaleDate ON SalesForecast
> (SaleDate)
> CREATE NONCLUSTERED INDEX Sales_SaleDate ON Sales (SaleDate)
>
>
>
> When I mix a query with both tables like this its really slow:
>
> SELECT A.S1 * B.F1
> FROM Sales A, SalesForecast B
> WHERE A.SaleDate = B.SaleDate
> AND A.CustID = B.CustID
> AND A.SaleDate > '20050101'
>
> What am I doing wrong, this seems to be correct..
>
>

30 Jul 2005 9:13 AM

David Portas

What are the keys in each case? Do you have any? A unique key could
make a significant difference.

--
David Portas
SQL Server MVP
--

30 Jul 2005 2:10 PM

--CELKO--

Do not use the proprietary MONEY data type. Declare a primary key for
the tables. Most codes are fixed length to make them easier to validate
so I find it hard to believe that your customer id is really VARCHAR(n)

CREATE TABLE SalesForecast
(sale_date DATETIME NOT NULL,
cust_id CHAR(10) NOT NULL,
forecast_amt DECIMAL (12,4) NOT NULL,
PRIMARY KEY (sale_date, cust_id));

CREATE TABLE Sales
(sale_date DATETIME NOT NULL,
cust_id CHAR(10) NOT NULL,
sales_amt DECIMAL (12,4) NOT NULL,
PRIMARY KEY (sale_date, cust_id));

This gives you a covering index for your query. If the join is still
slow, use a clustered option on the keys.

30 Jul 2005 10:41 PM

Rich

Thanks I'll give that a try. CustID is anywhere from 6 to 10 characters
long.. Most of the time it is 6, but sometimes it is 9 or 10! Should I still
use char instead of varchar?

Thanks.

Show quote

"--CELKO--" <jcelko***@earthlink.net> wrote in message
news:1122732638.691124.46520@g14g2000cwa.googlegroups.com...
> Do not use the proprietary MONEY data type. Declare a primary key for
> the tables. Most codes are fixed length to make them easier to validate
> so I find it hard to believe that your customer id is really VARCHAR(n)
>
> CREATE TABLE SalesForecast
> (sale_date DATETIME NOT NULL,
> cust_id CHAR(10) NOT NULL,
> forecast_amt DECIMAL (12,4) NOT NULL,
> PRIMARY KEY (sale_date, cust_id));
>
> CREATE TABLE Sales
> (sale_date DATETIME NOT NULL,
> cust_id CHAR(10) NOT NULL,
> sales_amt DECIMAL (12,4) NOT NULL,
> PRIMARY KEY (sale_date, cust_id));
>
> This gives you a covering index for your query. If the join is still
> slow, use a clustered option on the keys.
>