Wednesday, March 7, 2012

Indexes confusion

I have a temp table and created two indexes on it.
Please See below
--
CREATE TABLE #Invoice (
InvoiceID int NOT NULL,
CustomerKey varchar(20),
DebtorId int,
Reference varchar(50),
BalanceDue money,
DebtID int,
Reason varchar(100),
DebtStatus int,
)
CREATE INDEX idxInvoice ON #Invoice (InvoiceID)
CREATE INDEX idxInvoice2 ON #Invoice (DebtID)
---
I use DebtID as index because I use it again as a inner join to this table
in another query. So I assume it will make much faster. All I want to know is
would this code work?Already answered, please see your other thread, and these articles:
http://www.aspfaq.com/5007
http://www.aspfaq.com/5003
--
http://www.aspfaq.com/
(Reverse address to reply.)
"raj" <raj@.discussions.microsoft.com> wrote in message
news:0C4B1D69-3A7F-4202-B2DD-059127C6175C@.microsoft.com...
> I have a temp table and created two indexes on it.
> Please See below
> --
> CREATE TABLE #Invoice (
> InvoiceID int NOT NULL,
> CustomerKey varchar(20),
> DebtorId int,
> Reference varchar(50),
> BalanceDue money,
> DebtID int,
> Reason varchar(100),
> DebtStatus int,
> )
> CREATE INDEX idxInvoice ON #Invoice (InvoiceID)
> CREATE INDEX idxInvoice2 ON #Invoice (DebtID)
> ---
> I use DebtID as index because I use it again as a inner join to this table
> in another query. So I assume it will make much faster. All I want to know
is
> would this code work?
>|||I'm sorry , but I couldn't find the answer in the newsgroup.
"Aaron [SQL Server MVP]" wrote:
> Already answered, please see your other thread, and these articles:
> http://www.aspfaq.com/5007
> http://www.aspfaq.com/5003
> --
> http://www.aspfaq.com/
> (Reverse address to reply.)
>
>
> "raj" <raj@.discussions.microsoft.com> wrote in message
> news:0C4B1D69-3A7F-4202-B2DD-059127C6175C@.microsoft.com...
> > I have a temp table and created two indexes on it.
> > Please See below
> > --
> > CREATE TABLE #Invoice (
> > InvoiceID int NOT NULL,
> > CustomerKey varchar(20),
> > DebtorId int,
> > Reference varchar(50),
> > BalanceDue money,
> > DebtID int,
> > Reason varchar(100),
> > DebtStatus int,
> > )
> > CREATE INDEX idxInvoice ON #Invoice (InvoiceID)
> > CREATE INDEX idxInvoice2 ON #Invoice (DebtID)
> > ---
> >
> > I use DebtID as index because I use it again as a inner join to this table
> > in another query. So I assume it will make much faster. All I want to know
> is
> > would this code work?
> >
> >
>
>|||It should work fine. But bear in mind you're creating a temporary table
(with the # prefix on the tablename) so it will get created in tempdb and
will be automatically dropped when it goes out of scope (most likely at the
end of your batch). I don't know how much faster the indexes will really
make your queries - the best way to find out is trial and error (set a
couple session variables ("set statistics io on" and "set statistics time
on") at the beginning of your batch and turn on the execution plans to see
what real difference it makes).
Most temp table solutions don't benefit too much from indexes (since they're
usually pretty small tables, they're very short lived anyway and it takes
some resources to maintain the indexes). Both indexes you create in your
script are nonclustered which means your data itself is unsorted (it's a
"heap"). You might benefit from creating a clustered index on your table on
a suitable column, but as I just mentioned it'll probably take more
resources to maintain these indexes than it would to query the table without
the indexes in the first place. Trial & error is the only way to tell.
--
Cheers,
Mike
"raj" <raj@.discussions.microsoft.com> wrote in message
news:86C3A5FC-2F1E-4717-9130-7D372FB1B66E@.microsoft.com...
> I'm sorry , but I couldn't find the answer in the newsgroup.
> "Aaron [SQL Server MVP]" wrote:
>> Already answered, please see your other thread, and these articles:
>> http://www.aspfaq.com/5007
>> http://www.aspfaq.com/5003
>> --
>> http://www.aspfaq.com/
>> (Reverse address to reply.)
>>
>>
>> "raj" <raj@.discussions.microsoft.com> wrote in message
>> news:0C4B1D69-3A7F-4202-B2DD-059127C6175C@.microsoft.com...
>> > I have a temp table and created two indexes on it.
>> > Please See below
>> > --
>> > CREATE TABLE #Invoice (
>> > InvoiceID int NOT NULL,
>> > CustomerKey varchar(20),
>> > DebtorId int,
>> > Reference varchar(50),
>> > BalanceDue money,
>> > DebtID int,
>> > Reason varchar(100),
>> > DebtStatus int,
>> > )
>> > CREATE INDEX idxInvoice ON #Invoice (InvoiceID)
>> > CREATE INDEX idxInvoice2 ON #Invoice (DebtID)
>> > ---
>> >
>> > I use DebtID as index because I use it again as a inner join to this
>> > table
>> > in another query. So I assume it will make much faster. All I want to
>> > know
>> is
>> > would this code work?
>> >
>> >
>>|||Thank you very much for your help. Greatly appreciate.
raj
"Mike Hodgson" wrote:
> It should work fine. But bear in mind you're creating a temporary table
> (with the # prefix on the tablename) so it will get created in tempdb and
> will be automatically dropped when it goes out of scope (most likely at the
> end of your batch). I don't know how much faster the indexes will really
> make your queries - the best way to find out is trial and error (set a
> couple session variables ("set statistics io on" and "set statistics time
> on") at the beginning of your batch and turn on the execution plans to see
> what real difference it makes).
> Most temp table solutions don't benefit too much from indexes (since they're
> usually pretty small tables, they're very short lived anyway and it takes
> some resources to maintain the indexes). Both indexes you create in your
> script are nonclustered which means your data itself is unsorted (it's a
> "heap"). You might benefit from creating a clustered index on your table on
> a suitable column, but as I just mentioned it'll probably take more
> resources to maintain these indexes than it would to query the table without
> the indexes in the first place. Trial & error is the only way to tell.
> --
> Cheers,
> Mike
> "raj" <raj@.discussions.microsoft.com> wrote in message
> news:86C3A5FC-2F1E-4717-9130-7D372FB1B66E@.microsoft.com...
> > I'm sorry , but I couldn't find the answer in the newsgroup.
> >
> > "Aaron [SQL Server MVP]" wrote:
> >
> >> Already answered, please see your other thread, and these articles:
> >>
> >> http://www.aspfaq.com/5007
> >> http://www.aspfaq.com/5003
> >>
> >> --
> >> http://www.aspfaq.com/
> >> (Reverse address to reply.)
> >>
> >>
> >>
> >>
> >> "raj" <raj@.discussions.microsoft.com> wrote in message
> >> news:0C4B1D69-3A7F-4202-B2DD-059127C6175C@.microsoft.com...
> >> > I have a temp table and created two indexes on it.
> >> > Please See below
> >> > --
> >> > CREATE TABLE #Invoice (
> >> > InvoiceID int NOT NULL,
> >> > CustomerKey varchar(20),
> >> > DebtorId int,
> >> > Reference varchar(50),
> >> > BalanceDue money,
> >> > DebtID int,
> >> > Reason varchar(100),
> >> > DebtStatus int,
> >> > )
> >> > CREATE INDEX idxInvoice ON #Invoice (InvoiceID)
> >> > CREATE INDEX idxInvoice2 ON #Invoice (DebtID)
> >> > ---
> >> >
> >> > I use DebtID as index because I use it again as a inner join to this
> >> > table
> >> > in another query. So I assume it will make much faster. All I want to
> >> > know
> >> is
> >> > would this code work?
> >> >
> >> >
> >>
> >>
> >>
>
>

No comments:

Post a Comment