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

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..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...
> 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..
>|||What are the keys in each case? Do you have any? A unique key could
make a significant difference.
David Portas
SQL Server MVP
--|||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.|||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.
"--CELKO--" <jcelko212@.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.
>