Optimizing a JOIN

I have two tables.

One has approx 90,000 rows with a field .. let's call in BigInt (and it
is defined as a bigint data type).

I have a reference table, with approx 10,000,000 rows. In this
reference table, I have starting_bigint and ending_bigint fields. I
want to pull out all of the reference data from the reference table for
all 90,000 rows in the transaction table where the BigInt from the
transaction table is between the starting_bigint and ending_bigint in
the reference table.

I have the join working now, but it is not as optimized as I would
like. It appears no matter what I do, the query does a full table scan
on the 10,000,000 rows in the reference table.

Sample code

SELECT ref.*, tran.bigint
from transactiontable tran
INNER JOIN referencetable ref on tran.bigint between
ref.starting_bigint and ending_bigint

Yes, all 3 of the fields are indexed. I even have a composite index on
the reference table with the starting_bigint and ending_bigint fields
selected as the composite.

Any help would be appreciated.

Robert H. Kershberg
IT Director
Tax Credit Company
RKershberg@.taxcc.com or RKershberg@.pobox.com or RKershberg@.gmail.comIf the starting and ending bigints ranges are not overlapping, then I
would classify this as the "zipcode problem".

If this is the case, you could try the following approach:

SELECT R1.*,T1.bigint
FROM transactiontable T1
CROSS JOIN referencetable R1
WHERE R1.starting_bigint = (
SELECT MAX(starting_bigint)
FROM referencetable R2
WHERE R2.starting_bigint <= T1.bigint
)
AND R1.ending_bigint >= T1.bigint

Hope this helps,
Gert-Jan

"rkershberg@.gmail.com" wrote:
> I have two tables.
> One has approx 90,000 rows with a field .. let's call in BigInt (and it
> is defined as a bigint data type).
> I have a reference table, with approx 10,000,000 rows. In this
> reference table, I have starting_bigint and ending_bigint fields. I
> want to pull out all of the reference data from the reference table for
> all 90,000 rows in the transaction table where the BigInt from the
> transaction table is between the starting_bigint and ending_bigint in
> the reference table.
> I have the join working now, but it is not as optimized as I would
> like. It appears no matter what I do, the query does a full table scan
> on the 10,000,000 rows in the reference table.
> Sample code
> SELECT ref.*, tran.bigint
> from transactiontable tran
> INNER JOIN referencetable ref on tran.bigint between
> ref.starting_bigint and ending_bigint
> Yes, all 3 of the fields are indexed. I even have a composite index on
> the reference table with the starting_bigint and ending_bigint fields
> selected as the composite.
> Any help would be appreciated.
> Robert H. Kershberg
> IT Director
> Tax Credit Company
> RKershberg@.taxcc.com or RKershberg@.pobox.com or RKershberg@.gmail.com|||try skipping the "between." something like
SELECT ref.*, tran.bigint
from transactiontable tran
INNER JOIN referencetable ref
on tran.bigint >= ref.starting_bigint
and tran.bigint <=ref.ending_bigint

try creating a composite index on ref, containing starting_bigint plus
ending_bigint
if that doesn't work, try zapping your existing indexes on ref.

thinking outside the box, try creating staging tables, or ghost tables.
The ghost tables are for a selected period taht you are currently
working on. You copy pieces of your trans table off to a temp table,
and do your lookups against it. you eat the one copy, you eat the
creation of the indexon the temp table, but you get the benefits of not
having to do complex searches against a 100,000,000 row table. this is
RARELY needed, but I've done it on rare occasion.|||Thank you for your time. I had tried a cross join, but not quite the
same way. It'll be interesting to see if there is a significant
difference. I appreciate your intelligent input.

Rob|||Thanks again .. this logic improved performance 3 to 4 fold (I had
already cut a 2 min 30 sec query to 15 seconds .. your logic
implemented on top of mine cut that down to 3 seconds .. this on 88,000
row transacation table running against a 9.6 million row reference
table .. not zip codes, but the right idea <g>).

Thank YOU very much!!!

Rob|||3 seconds is a long time.

how do we make it faster??

i am not a huge fan of clustered indexes. However for a reference
table where you will ALWAYS be looking up data utilizing a specific
column, and rarely inserting into the middle, I'd sure consider it.

For grins, try creating a clustered, composite index on starting_bigint
+ ending_bigint.

For grins after that, consider a clustered index on trans.bigint.
warning: clustered indexes on trans tables are usually not a great idea
IMO, unless the clustered index is based on a timestamp or an identity
column.

-doug

Optimizator and indexes

Is there a way to force optimizer to use indexes without hints? (some
server setting or index type...)
I'll give an example to clarify :

I have a table with fields
Customer_Code char(10) not null
Invoice_Numberintnot null
and an index on those fields IX_1.

there are about 2,000,000 records in the table and those two fields are
not unique, and I cant use clustered index because of the nature of the
table (need it for something else).

When I use query like :

Select * from CustInv where Customer_Code='ABC' and invoice_Number=2

depending on the ammount of data statistics computes, query is executed
using the IX_1 or IX_1 is ignored. The documentation says that using of
indexes is determened by the uniqueness of the data, but my tests show
that every usage of index is faster.
I can fool the optimizer when using query like

Select * from CustInv where Customer_Code='ABC' and invoice_Number>=2
and invoice_number<=2

but all that it does is extends the optimizer's tolerance (IX_1) is
ignored for some queries in that form also.

Because my database and indexes are dynamically created, I cannot use
hints for indexes, so if anyone knows a way to tell the database
something like : "If you have an index on where fields, use it ALWAYS",
the knowledge would be gratly appretiated! :)

Thanx in advance! Sorry for a longer post and maybe fuzzy explanation of
the problem...

IgorIgor,

If you create the tables (and indexes) dynamically, then it should be no
problem for you to name your indexes and constraints. And if you can
name your indexes/constraints, then you can use index hints.

BTW: There is no way to force the use of a particular index without
using hints. Such option would make no sense at all.

SQL-Server uses a cost based optimizer and will minimize I/O, it will
choose indexes that will minimize query execution times.

With a hot cache, an index hint can in some situations increase
performance. Note however that the index hint can become troublesome
when the amount of data grows (or unused space increases), or when the
cache size shrinks (because other processes need the server's memory).

If the query that you are actually using is more complex then the one
below, then please post it. There may be other options...

Hope this helps,
Gert-Jan

Igor wrote:
> Is there a way to force optimizer to use indexes without hints? (some
> server setting or index type...)
> I'll give an example to clarify :
> I have a table with fields
> Customer_Code char(10) not null
> Invoice_Number int not null
> and an index on those fields IX_1.
> there are about 2,000,000 records in the table and those two fields are
> not unique, and I cant use clustered index because of the nature of the
> table (need it for something else).
> When I use query like :
> Select * from CustInv where Customer_Code='ABC' and invoice_Number=2
> depending on the ammount of data statistics computes, query is executed
> using the IX_1 or IX_1 is ignored. The documentation says that using of
> indexes is determened by the uniqueness of the data, but my tests show
> that every usage of index is faster.
> I can fool the optimizer when using query like
> Select * from CustInv where Customer_Code='ABC' and invoice_Number>=2
> and invoice_number<=2
> but all that it does is extends the optimizer's tolerance (IX_1) is
> ignored for some queries in that form also.
> Because my database and indexes are dynamically created, I cannot use
> hints for indexes, so if anyone knows a way to tell the database
> something like : "If you have an index on where fields, use it ALWAYS",
> the knowledge would be gratly appretiated! :)
> Thanx in advance! Sorry for a longer post and maybe fuzzy explanation of
> the problem...
> Igor|||Igor (bija@.baja.com) writes:
> Because my database and indexes are dynamically created, I cannot use
> hints for indexes, so if anyone knows a way to tell the database
> something like : "If you have an index on where fields, use it ALWAYS",
> the knowledge would be gratly appretiated! :)

Believe me, you don't want that. Say that you have:

SELECT * FROM tbl WHERE nonclusteredindexedcol = @.value

If there is a good distribution of values in nonclusteredindexedcol,
the index will be good. But say that for 30% of the rows, the value
is the one the same. The cost for using the index rather than scanning
the table, will be considerably lower than using the index, since when
using the index the same page will be accessed more than once.

It is true, though, that the optimizer is overly conservative when it
comes to using non-clustered index, and in my opinion, the threshold
when it switches to table scan is a little low.

For a table where I know my data, I don't shudder for using an index
hint. But for a dynamically created database like yours, this sounds
risky.

A better alternative might be to build non-clustered indexes which
comprises all columns you need. If that is all columns of the table,
that is in practice a second clustered index on the table. It may
not be good for update speed though.

You could also investigate indexed views.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Thanks for the reply.

Unfortunately, the query is as simple as I posted.
I didn't mean forcing usage of an PARTICULAR index, but telling the
optimizer if there is an index on the table with my where clause, use
it. :)
What I ment by dynamically creating indexes is that some tables index
themselves depending on the usage and data in them. The index names are
created dynamically and for me to choose one would mean scanning the
where clause and index names table...

And, if the only way to get the optimizer to actually optimize is to
turn it off, I can use DBF+NTX :))

btw, the irony is that the more complex the query, the smarter the
optimizer gets! :((

Gert-Jan Strik wrote:
> Igor,
> If you create the tables (and indexes) dynamically, then it should be no
> problem for you to name your indexes and constraints. And if you can
> name your indexes/constraints, then you can use index hints.
> BTW: There is no way to force the use of a particular index without
> using hints. Such option would make no sense at all.
> SQL-Server uses a cost based optimizer and will minimize I/O, it will
> choose indexes that will minimize query execution times.
> With a hot cache, an index hint can in some situations increase
> performance. Note however that the index hint can become troublesome
> when the amount of data grows (or unused space increases), or when the
> cache size shrinks (because other processes need the server's memory).
> If the query that you are actually using is more complex then the one
> below, then please post it. There may be other options...
> Hope this helps,
> Gert-Jan|||Thanks for the reply!
I agree with you on the threshold of the optimizer. But my problem
manifests itself like this :

My table is about 2,000,000 records, with an estimated growth of about
250,000 a month (don't think a giant index would be nice :))

When a result set will contain 3 records the query time is about 20secs
(stupid thing scans the table)

When a result set will contain 50,000 records (or so the optimizer
thinks), the query time is about 5 secs!
(uses index)
When I show this to my user, I get beat up by a stick! :))

Also, the server has only 1G of ram, so it cannot cache everything (if
it could, i wouldn't have noticed this) and is a dedicated server (only
SQL2000).
Not dealing with the inteligence (stupidity?) of the optimizer, is there
a way to increase the threshold for the table scan?

database is optimized every night and is currently about 10G!

Erland Sommarskog wrote:
> Igor (bija@.baja.com) writes:
>>Because my database and indexes are dynamically created, I cannot use
>>hints for indexes, so if anyone knows a way to tell the database
>>something like : "If you have an index on where fields, use it ALWAYS",
>>the knowledge would be gratly appretiated! :)
>
> Believe me, you don't want that. Say that you have:
> SELECT * FROM tbl WHERE nonclusteredindexedcol = @.value
> If there is a good distribution of values in nonclusteredindexedcol,
> the index will be good. But say that for 30% of the rows, the value
> is the one the same. The cost for using the index rather than scanning
> the table, will be considerably lower than using the index, since when
> using the index the same page will be accessed more than once.
> It is true, though, that the optimizer is overly conservative when it
> comes to using non-clustered index, and in my opinion, the threshold
> when it switches to table scan is a little low.
> For a table where I know my data, I don't shudder for using an index
> hint. But for a dynamically created database like yours, this sounds
> risky.
> A better alternative might be to build non-clustered indexes which
> comprises all columns you need. If that is all columns of the table,
> that is in practice a second clustered index on the table. It may
> not be good for update speed though.
> You could also investigate indexed views.|||Hi Igor
have you tried manually updating statistics more often?|||Tzvika Barenholz wrote:
> Hi Igor
> have you tried manually updating statistics more often?

Yes. Database has a plan to do it every night. but optimizer decides on
the basis of the statistics which is bad in my case... :(|||Igor,

If table CustInv has a nonclustered compound index on
(Customer_Code,Invoice_Number) and you run the query

Select * from CustInv where Customer_Code='ABC' and invoice_Number=2

and this query (0) will only return a few rows, then I would be *very*
surprised if the optimizer did not use the nonclustered index.

However, if what you are actually doing is this (1)

EXEC MySProc 'ABC', 2

or this (2)

Declare @.Customer_Code char(10)
Declare @.invoice_Number int
Set @.Customer_Code='ABC'
Set @.invoice_Number=2
Select * from CustInv where Customer_Code=@.Customer_Code and
invoice_Number=@.invoice_Number

then that is a different story.

Situation 1 is different because of parameter sniffing (check out Google
for more info). If you are using that, adding WITH RECOMPILE to the
stored procdure definition could be a solution.

In situation 2, it is not the statistics of the value pair ('ABC',2)
that is used, but the selectivity of the entire index, because the
variable values are not known compile time. The query optimizer will
then create a plan for the worst case scenario. If your data is evenly
distributed, then it could use the nonclustered index. However, if your
data distribution is skewed, and there is a combination with a very high
occurrence, then SQL-Server will probably choose a query plan with a
clustered index scan. Whatever query plan is chosen, it is used for all
different variable values.

Please let us know which type of query is actually causing the bad
performance: Type 0, 1 or 2.

Gert-Jan

Igor wrote:
> Thanks for the reply.
> Unfortunately, the query is as simple as I posted.
> I didn't mean forcing usage of an PARTICULAR index, but telling the
> optimizer if there is an index on the table with my where clause, use
> it. :)
> What I ment by dynamically creating indexes is that some tables index
> themselves depending on the usage and data in them. The index names are
> created dynamically and for me to choose one would mean scanning the
> where clause and index names table...
> And, if the only way to get the optimizer to actually optimize is to
> turn it off, I can use DBF+NTX :))
> btw, the irony is that the more complex the query, the smarter the
> optimizer gets! :((
> Gert-Jan Strik wrote:
> > Igor,
> > If you create the tables (and indexes) dynamically, then it should be no
> > problem for you to name your indexes and constraints. And if you can
> > name your indexes/constraints, then you can use index hints.
> > BTW: There is no way to force the use of a particular index without
> > using hints. Such option would make no sense at all.
> > SQL-Server uses a cost based optimizer and will minimize I/O, it will
> > choose indexes that will minimize query execution times.
> > With a hot cache, an index hint can in some situations increase
> > performance. Note however that the index hint can become troublesome
> > when the amount of data grows (or unused space increases), or when the
> > cache size shrinks (because other processes need the server's memory).
> > If the query that you are actually using is more complex then the one
> > below, then please post it. There may be other options...
> > Hope this helps,
> > Gert-Jan|||Gert-Jan,
Unfortunately, query (0) is the one with a problem! :)
But, i've tried q(2) and this works fine??
Also, I've tried this on a box with 4G of ram - works perfectly (not the
speed, the plan)... If only I could find a way to increase the threshold
for I/O performance... :)

Anyway, thanks a lot for a great post - i'll play around with it some
more, but my nerves are breaking... :)

Gert-Jan Strik wrote:
> Igor,
> If table CustInv has a nonclustered compound index on
> (Customer_Code,Invoice_Number) and you run the query
> Select * from CustInv where Customer_Code='ABC' and invoice_Number=2
> and this query (0) will only return a few rows, then I would be *very*
> surprised if the optimizer did not use the nonclustered index.
> However, if what you are actually doing is this (1)
> EXEC MySProc 'ABC', 2
> or this (2)
> Declare @.Customer_Code char(10)
> Declare @.invoice_Number int
> Set @.Customer_Code='ABC'
> Set @.invoice_Number=2
> Select * from CustInv where Customer_Code=@.Customer_Code and
> invoice_Number=@.invoice_Number
> then that is a different story.
> Situation 1 is different because of parameter sniffing (check out Google
> for more info). If you are using that, adding WITH RECOMPILE to the
> stored procdure definition could be a solution.
> In situation 2, it is not the statistics of the value pair ('ABC',2)
> that is used, but the selectivity of the entire index, because the
> variable values are not known compile time. The query optimizer will
> then create a plan for the worst case scenario. If your data is evenly
> distributed, then it could use the nonclustered index. However, if your
> data distribution is skewed, and there is a combination with a very high
> occurrence, then SQL-Server will probably choose a query plan with a
> clustered index scan. Whatever query plan is chosen, it is used for all
> different variable values.
> Please let us know which type of query is actually causing the bad
> performance: Type 0, 1 or 2.
> Gert-Jan
>
> Igor wrote:
>>Thanks for the reply.
>>
>>Unfortunately, the query is as simple as I posted.
>>I didn't mean forcing usage of an PARTICULAR index, but telling the
>>optimizer if there is an index on the table with my where clause, use
>>it. :)
>>What I ment by dynamically creating indexes is that some tables index
>>themselves depending on the usage and data in them. The index names are
>>created dynamically and for me to choose one would mean scanning the
>>where clause and index names table...
>>
>>And, if the only way to get the optimizer to actually optimize is to
>>turn it off, I can use DBF+NTX :))
>>
>>btw, the irony is that the more complex the query, the smarter the
>>optimizer gets! :((
>>
>>Gert-Jan Strik wrote:
>>
>>>Igor,
>>>
>>>If you create the tables (and indexes) dynamically, then it should be no
>>>problem for you to name your indexes and constraints. And if you can
>>>name your indexes/constraints, then you can use index hints.
>>>
>>>BTW: There is no way to force the use of a particular index without
>>>using hints. Such option would make no sense at all.
>>>
>>>SQL-Server uses a cost based optimizer and will minimize I/O, it will
>>>choose indexes that will minimize query execution times.
>>>
>>>With a hot cache, an index hint can in some situations increase
>>>performance. Note however that the index hint can become troublesome
>>>when the amount of data grows (or unused space increases), or when the
>>>cache size shrinks (because other processes need the server's memory).
>>>
>>>If the query that you are actually using is more complex then the one
>>>below, then please post it. There may be other options...
>>>
>>>Hope this helps,
>>>Gert-Jan|||Igor (bija@.baja.com) writes:
> Thanks for the reply!
> I agree with you on the threshold of the optimizer. But my problem
> manifests itself like this :
> My table is about 2,000,000 records, with an estimated growth of about
> 250,000 a month (don't think a giant index would be nice :))
> When a result set will contain 3 records the query time is about 20secs
> (stupid thing scans the table)
> When a result set will contain 50,000 records (or so the optimizer
> thinks), the query time is about 5 secs!
> (uses index)
> When I show this to my user, I get beat up by a stick! :))

Not seeing the table, its indexes and its plan, it's difficult to say
something useful.

But it is worth keeping in mind that stastistics are normally maintained
for single columns, if your query has two conditions of which each
value is fairly common, but the combination is not, the optimizer
may be confused. You can create two-column statistics, but I don't know
whether they actually have any practical importance.

Assuming that your query is dynamically generated, you could add an
index hint like:

WITH INDEX (2, 3, 4, ...)

where you simply include the index ids of the non-clustered indexes
of the table. You would have to query sysindexes to find them. (You
need to exclude statistics and hypothetical indexes.)

If that doesn't work out, you probably need to reconsider your
strategy with dynamic indexing.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

Optimise these 3 queries into one?

I have these 3 queries - they are the same except each fetches record counts
for one of 3 different record types, nSubsets (type 0), nAssets (type 1) and
nImages (type 2). Is there any way I could get all 3 of these (based on the
Node.Type integer) with a single query?

IF @.Error = 0
BEGIN
SELECT @.nSubsets = COUNT(*)
FROM Node
INNER JOIN Adjacency
ON Adjacency.ID_Node = Node.ID
WHERE Adjacency.Path LIKE @.nodepath + '%'
AND
Node.Type = 0

SET @.Error = @.ERROR
END

IF @.Error = 0
BEGIN
SELECT @.nAssets = COUNT(*)
FROM Node
INNER JOIN Adjacency
ON Adjacency.ID_Node = Node.ID
WHERE Adjacency.Path LIKE @.nodepath + '%'
AND
Node.Type = 1

SET @.Error = @.ERROR
END

IF @.Error = 0
BEGIN
SELECT @.nImages = COUNT(*)
FROM Node
INNER JOIN Adjacency
ON Adjacency.ID_Node = Node.ID
WHERE Adjacency.Path LIKE @.nodepath + '%'
AND
Node.Type = 2

SET @.Error = @.ERROR
ENDSELECT
@.nSubsets = COUNT(CASE Node.type WHEN 0 THEN 1 END),
@.nAssets = COUNT(CASE Node.type WHEN 1 THEN 1 END),
@.nImages = COUNT(CASE Node.type WHEN 2 THEN 1 END)
FROM Node
INNER JOIN Adjacency
ON Adjacency.ID_Node = Node.ID
WHERE Adjacency.Path LIKE @.nodepath + '%'

--
David Portas
----
Please reply only to the newsgroup
--|||Robin,

I'm assuming you wanted the result in one row. This is untested, but
you get the idea...

IF @.Error = 0
BEGIN
SELECT SUM (CASE Node.Type
WHEN 0 THEN 1
ELSE 0
END) AS nSubSets_cnt
,SUM (CASE Node.Type
WHEN 1 THEN 1
ELSE 0
END) AS nAssets_cnt
,SUM (CASE Node.Type
WHEN 2 THEN 1
ELSE 0
END) AS nImages_cnt
FROM Node
INNER JOIN
Adjacency
ON Adjacency.ID_Node = Node.ID
WHERE Adjacency.Path LIKE @.nodepath + '%'
AND Node.Type = in (0, 1, 2)

SET @.Error = @.ERROR
END

Christian.|||How about this?

SELECT @.nSubsets = sum(case when node.type = 0 then 1 else 0 end),
@.nAssets = sum(case when node.type = 1 then 1 else 0 end),
@.nImages = sum(case when node.type = 2 then 1 else 0 end),
FROM Node
INNER JOIN Adjacency
ON Adjacency.ID_Node = Node.ID
WHERE Adjacency.Path LIKE @.nodepath + '%'

I think tyhis will get you what you are looking for. I haven't run it
against anything, but I'm fairly sure it's good to go.

Hope it works for you,
Carl

"Robin Tucker" <idontwanttobespammedanymore@.reallyidont.com> wrote in message news:<bs9jhc$gfo$1$8300dec7@.news.demon.co.uk>...
> I have these 3 queries - they are the same except each fetches record counts
> for one of 3 different record types, nSubsets (type 0), nAssets (type 1) and
> nImages (type 2). Is there any way I could get all 3 of these (based on the
> Node.Type integer) with a single query?
> IF @.Error = 0
> BEGIN
> SELECT @.nSubsets = COUNT(*)
> FROM Node
> INNER JOIN Adjacency
> ON Adjacency.ID_Node = Node.ID
> WHERE Adjacency.Path LIKE @.nodepath + '%'
> AND
> Node.Type = 0
> SET @.Error = @.ERROR
> END
> IF @.Error = 0
> BEGIN
> SELECT @.nAssets = COUNT(*)
> FROM Node
> INNER JOIN Adjacency
> ON Adjacency.ID_Node = Node.ID
> WHERE Adjacency.Path LIKE @.nodepath + '%'
> AND
> Node.Type = 1
> SET @.Error = @.ERROR
> END
> IF @.Error = 0
> BEGIN
> SELECT @.nImages = COUNT(*)
> FROM Node
> INNER JOIN Adjacency
> ON Adjacency.ID_Node = Node.ID
> WHERE Adjacency.Path LIKE @.nodepath + '%'
> AND
> Node.Type = 2
> SET @.Error = @.ERROR
> END|||Use the CASE function.
EG:

select

CASE when Adjacency.Path LIKE @.nodepath + '%' AND Node.Type = 0 then 1
else 0 end "NT=0",
CASE when Adjacency.Path LIKE @.nodepath + '%' AND Node.Type = 1 then 1
else 0 end "NT=1",
CASE when Adjacency.Path LIKE @.nodepath + '%' AND Node.Type = 2 then 1
else 0 end "NT=2",

from ...

"Robin Tucker" <idontwanttobespammedanymore@.reallyidont.com> wrote in
message news:bs9jhc$gfo$1$8300dec7@.news.demon.co.uk...
> I have these 3 queries - they are the same except each fetches record
counts
> for one of 3 different record types, nSubsets (type 0), nAssets (type 1)
and
> nImages (type 2). Is there any way I could get all 3 of these (based on
the
> Node.Type integer) with a single query?
> IF @.Error = 0
> BEGIN
> SELECT @.nSubsets = COUNT(*)
> FROM Node
> INNER JOIN Adjacency
> ON Adjacency.ID_Node = Node.ID
> WHERE Adjacency.Path LIKE @.nodepath + '%'
> AND
> Node.Type = 0
> SET @.Error = @.ERROR
> END
> IF @.Error = 0
> BEGIN
> SELECT @.nAssets = COUNT(*)
> FROM Node
> INNER JOIN Adjacency
> ON Adjacency.ID_Node = Node.ID
> WHERE Adjacency.Path LIKE @.nodepath + '%'
> AND
> Node.Type = 1
> SET @.Error = @.ERROR
> END
> IF @.Error = 0
> BEGIN
> SELECT @.nImages = COUNT(*)
> FROM Node
> INNER JOIN Adjacency
> ON Adjacency.ID_Node = Node.ID
> WHERE Adjacency.Path LIKE @.nodepath + '%'
> AND
> Node.Type = 2
> SET @.Error = @.ERROR
> END|||All good stuff. Thanks for all 3 replys.

"Carl Reeds" <creeds0001@.msn.com> wrote in message
news:956eb1fa.0312231411.4e7cec31@.posting.google.c om...
> How about this?
> SELECT @.nSubsets = sum(case when node.type = 0 then 1 else 0 end),
> @.nAssets = sum(case when node.type = 1 then 1 else 0 end),
> @.nImages = sum(case when node.type = 2 then 1 else 0 end),
> FROM Node
> INNER JOIN Adjacency
> ON Adjacency.ID_Node = Node.ID
> WHERE Adjacency.Path LIKE @.nodepath + '%'
> I think tyhis will get you what you are looking for. I haven't run it
> against anything, but I'm fairly sure it's good to go.
> Hope it works for you,
> Carl
>
> "Robin Tucker" <idontwanttobespammedanymore@.reallyidont.com> wrote in
message news:<bs9jhc$gfo$1$8300dec7@.news.demon.co.uk>...
> > I have these 3 queries - they are the same except each fetches record
counts
> > for one of 3 different record types, nSubsets (type 0), nAssets (type 1)
and
> > nImages (type 2). Is there any way I could get all 3 of these (based on
the
> > Node.Type integer) with a single query?
> > IF @.Error = 0
> > BEGIN
> > SELECT @.nSubsets = COUNT(*)
> > FROM Node
> > INNER JOIN Adjacency
> > ON Adjacency.ID_Node = Node.ID
> > WHERE Adjacency.Path LIKE @.nodepath + '%'
> > AND
> > Node.Type = 0
> > SET @.Error = @.ERROR
> > END
> > IF @.Error = 0
> > BEGIN
> > SELECT @.nAssets = COUNT(*)
> > FROM Node
> > INNER JOIN Adjacency
> > ON Adjacency.ID_Node = Node.ID
> > WHERE Adjacency.Path LIKE @.nodepath + '%'
> > AND
> > Node.Type = 1
> > SET @.Error = @.ERROR
> > END
> > IF @.Error = 0
> > BEGIN
> > SELECT @.nImages = COUNT(*)
> > FROM Node
> > INNER JOIN Adjacency
> > ON Adjacency.ID_Node = Node.ID
> > WHERE Adjacency.Path LIKE @.nodepath + '%'
> > AND
> > Node.Type = 2
> > SET @.Error = @.ERROR
> > END

Optimal Placement of Data/Log Files

What is the best way to setup a server for hosting SQL Sever 2005? If I could get input of type of RAID to use and where to place the files that would be very appreciated.

Thanks,

Todd Sparks

Hey Todd. Well, ideally we'd always recommend RAID 10 if possible for both data and log file LUNs for performance and availability reasons. If that's not possible due to cost, I'd recommend a RAID 1 configuration for your log file and a RAID 5 for data files...ideally, if cost is not prohibitive again RAID 1 for data files would probaby be better than 5.

For a more in depth look at the internals of SQL Server IO see the following paper:
http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/sqlIObasics.mspx

In addition, check out the following paper on disk subsystem performance:
http://www.microsoft.com/whdc/device/storage/subsys_perf.mspx

HTH

Operator is not valid for type 'Date' and type 'Date'

I was wondering if someone could assist me in writing an experession that
would subtract 2 DateTime's. I have 2 DateTime fields and want to display
both in a report and in a third column the difference between the 2 values.
However, I get an error:
"Operator is not valid for type 'Date' and type 'Date'"
Many thanks,
SimonYou can get the difference from SQL by trying something like this in your
store proc or query
SELECT date1, date2, DATEDIFF(d, date1, date2) AS date3
hope it helps.
- David
"Simon Dingley" wrote:
> I was wondering if someone could assist me in writing an experession that
> would subtract 2 DateTime's. I have 2 DateTime fields and want to display
> both in a report and in a third column the difference between the 2 values.
> However, I get an error:
> "Operator is not valid for type 'Date' and type 'Date'"
> Many thanks,
> Simon
>
>|||Assuming you have two DateTime fields and you want to determine the
difference in total seconds in the report rather than the query, you can use
an expression like this:
=Datediff("s", Fields!End_Time.Value, Fields!Start_Time.Value)
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"David Nevarez" <DavidNevarez@.discussions.microsoft.com> wrote in message
news:33B5D244-F9D5-4ECB-99CB-F9B1B7D9E445@.microsoft.com...
> You can get the difference from SQL by trying something like this in your
> store proc or query
> SELECT date1, date2, DATEDIFF(d, date1, date2) AS date3
> hope it helps.
> - David
> "Simon Dingley" wrote:
> > I was wondering if someone could assist me in writing an experession
that
> > would subtract 2 DateTime's. I have 2 DateTime fields and want to
display
> > both in a report and in a third column the difference between the 2
values.
> > However, I get an error:
> >
> > "Operator is not valid for type 'Date' and type 'Date'"
> >
> > Many thanks,
> >
> > Simon
> >
> >
> >|||Thank You for the replies. I opted for the in-report solution as opposed to
the SQL solution but thanks for both. What I want to return is the
difference in days and hours but can seem to do it I changed the format
string to "d" instead of "s" to return the number of days but when I tried
to use "d h" i get "#error".
Thanks for the help.
Simon
"Robert Bruckner [MSFT]" <robruc@.online.microsoft.com> wrote in message
news:eVK4OnNsEHA.832@.TK2MSFTNGP10.phx.gbl...
> Assuming you have two DateTime fields and you want to determine the
> difference in total seconds in the report rather than the query, you can
use
> an expression like this:
> =Datediff("s", Fields!End_Time.Value, Fields!Start_Time.Value)
> --
> This posting is provided "AS IS" with no warranties, and confers no
rights.

Operand type clash: nvarchar is incompatible with image

I uploading an image from a web page into Sql Server 2000 database. I call a stored procedure with the parameter @.Flag of type Image. When I tried to pass a null value to this parameter I got the error:
"Operand type clash: nvarchar is incompatible with image".

I was adding the parameter like this:
sqlCmd.Parameters.AddWithValue("@.Flag", DBNull.Value);

After trying few different things I found a workaround using the following:
sqlCmd.Parameters.Add("@.Flag", SqlDbType.Image);
sqlCmd.Parameters["@.Flag"].Value = DBNull.Value;

Possibly a bug in the SqlCommand.Parameters.AddWithValue method ?

Not a bug. It's a limititation. If you don't specify a type (like with AddWithValue), then it makes a best guess. In this case, it guesses wrong.

Operand type clash: datetime is incompatible with text

I am getting this error on my insert statement, do I need to do something
for my datetime fields?
Here is the statement
INSERT INTO tblCalendar (
adjName, DispDueDate, ClaimNumber, Juris, HearingDate, HearingType,
ClaimantName, Location, HearingTime, HearingPart, WCB#, Counsel,
DateRecd)
VALUES (
@.Adjuster, @.newDispositionDueDate, @.ClaimNumber, @.Juris, @.newHearingDate,
@.HearingType, @.ClaimantName, @.HearingLocation, @.newHearingTime,
@.HearingPartRoom, @.WCBClaimNumber, @.AssignedCounsel, @.newNoticeReceived)You might want to check the datatypes of each column in tblCalendar table
and make sure they are compatible with the variables in the VALUES() clause.
If you have difficulty, post the exact CREATE TABLE statements and the
declared types and assigned values for each variables in the INSERT
statement.
Anith

Operand type clash error

Hello!

I am trying to update a DetailsView but I keep getting the error of Operand type clash: int is incompatible with text. I have double checked the stored procedure but I don't see any obvious offending parameters. ID is the only parameter that is an interger. I am not trying to update the ID at all, just for the filter purposes to update the records.

ALTER PROCEDURE [dbo].[proc_update]
/*
(
@.parameter1 int = 5,
@.parameter2 datatype OUTPUT
)
*/
@.BUILDING_SV float,
@.BUILDING_ADDS float,
@.BUILDING_DEL float,
@.BUILDING_TOTAL float,
@.ME_SV float,
@.ME_ADDS float,
@.ME_DEL float,
@.ME_TOTAL float,
@.PATTERNS_SV float,
@.PATTERNS_ADDS float,
@.PATTERNS_DEL float,
@.PATTERNS_TOTAL float,
@.SS_VALUE float,
@.SS_TOT float,
@.COMMENTS text,
@.OCCUPANCY_TYPE varchar(255),
@.REVISED_BY varchar(255),
@.DRAWINGS float,
@.INVENTORY float,
@.TOTAL float,
@.DIVISION varchar(255),
@.LOCATION varchar(255),
@.LOCATIONCODE varchar(255),
@.ADDRESS1 varchar(255),
@.ADDRESS2 varchar(255),
@.ADDRESS3 varchar(255),
@.ADDRESS4 varchar(255),
@.PROP_ID float,
@.POST_DATE smalldatetime,
@.ID int
AS
/* SET NOCOUNT ON */
UPDATE dbo.PROPERTY_VALUES
SET BUILDING_SV = @.BUILDING_SV, BUILDING_ADDS = @.BUILDING_ADDS, BUILDING_DEL = @.BUILDING_DEL, BUILDING_TOTAL = @.BUILDING_TOTAL, ME_SV = @.ME_SV, ME_ADDS = @.ME_ADDS, ME_DEL = @.ME_DEL, ME_TOTAL = @.ME_TOTAL, PATTERNS_SV = @.PATTERNS_SV, PATTERNS_ADDS = @.PATTERNS_ADDS, PATTERNS_DEL = @.PATTERNS_DEL, PATTERNS_TOTAL = @.PATTERNS_TOTAL, SS_VALUE = @.SS_VALUE, SS_TOT = @.SS_TOT, COMMENTS = @.COMMENTS,OCCUPANCY_TYPE = @.OCCUPANCY_TYPE, REVISED_BY = @.REVISED_BY, DRAWINGS = @.DRAWINGS, INVENTORY = @.INVENTORY, TOTAL = @.TOTAL, DIVISION = @.DIVISION, LOCATION = @.LOCATION, LOCATIONCODE = @.LOCATIONCODE, ADDRESS1 = @.ADDRESS1, ADDRESS2 = @.ADDRESS2, ADDRESS3 = @.ADDRESS3, ADDRESS4 = @.ADDRESS4, PROP_ID = @.PROP_ID, POST_DATE = @.POST_DATE
WHERE [ID] = @.ID
RETURN

Help!

Thanks.

I did more debugging. I discovered that the null values in the DetailsView are passing as "1" to the database. Anyone know how to debug this? Thanks.

|||

You probably have some default values set to 1, and "ConvertNullToDefaultValue" set to true (I forget the exact property name, but it's close).

|||

Thanks.

I don't recall setting the default values to 1. I am stumped why it is doing this. Is CONVERTNULLTODEFAULTVALUE a part of the codebehind or in the source page?

Thanks!

|||Please post the sqldatasource source (Everything between <asp:SqlDataSource and </asp:SqlDataSource>).|||

Here is it.

<asp:SqlDataSourceID="Update"runat="server"

SelectCommand="proc_update_select"UpdateCommand="proc_update"SelectCommandType="StoredProcedure"UpdateCommandType="StoredProcedure">

<UpdateParameters>

<asp:ControlParameterControlID="DetailsView1"Name="BUILDING_SV"PropertyName="SelectedValue"/>

<asp:ControlParameterControlID="DetailsView1"Name="BUILDING_ADDS"PropertyName="SelectedValue"/>

<asp:ControlParameterControlID="DetailsView1"Name="BUILDING_DEL"PropertyName="SelectedValue"/>

<asp:ControlParameterControlID="DetailsView1"Name="BUILDING_TOTAL"PropertyName="SelectedValue"/>

<asp:ControlParameterControlID="DetailsView1"Name="ME_SV"PropertyName="SelectedValue"/>

<asp:ControlParameterControlID="DetailsView1"Name="ME_ADDS"PropertyName="SelectedValue"/>

<asp:ControlParameterControlID="DetailsView1"Name="ME_DEL"PropertyName="SelectedValue"/>

<asp:ControlParameterControlID="DetailsView1"Name="ME_TOTAL"PropertyName="SelectedValue"/>

<asp:ControlParameterControlID="DetailsView1"Name="PATTERNS_SV"PropertyName="SelectedValue"/>

<asp:ControlParameterControlID="DetailsView1"Name="PATTERNS_ADDS"PropertyName="SelectedValue"/>

<asp:ControlParameterControlID="DetailsView1"Name="PATTERNS_DEL"PropertyName="SelectedValue"/>

<asp:ControlParameterControlID="DetailsView1"Name="PATTERNS_TOTAL"PropertyName="SelectedValue"/>

<asp:ControlParameterControlID="DetailsView1"Name="SS_VALUE"PropertyName="SelectedValue"/>

<asp:ControlParameterControlID="DetailsView1"Name="SS_TOT"PropertyName="SelectedValue"/>

<asp:ControlParameterControlID="DetailsView1"Name="COMMENTS"PropertyName="SelectedValue"/>

<asp:ControlParameterControlID="DetailsView1"Name="OCCUPANCY_TYPE"PropertyName="SelectedValue"/>

<asp:ControlParameterControlID="DetailsView1"Name="POST_DATE"PropertyName="SelectedValue"/>

<asp:ControlParameterControlID="DetailsView1"Name="REVISED_BY"PropertyName="SelectedValue"/>

<asp:ControlParameterControlID="DetailsView1"Name="DRAWINGS"PropertyName="SelectedValue"/>

<asp:ControlParameterControlID="DetailsView1"Name="INVENTORY"PropertyName="SelectedValue"/>

<asp:ControlParameterControlID="DetailsView1"Name="TOTAL"PropertyName="SelectedValue"/>

<asp:ControlParameterControlID="DetailsView1"Name="DIVISION"PropertyName="SelectedValue"/>

<asp:ControlParameterControlID="DetailsView1"Name="LOCATION"PropertyName="SelectedValue"/>

<asp:ControlParameterControlID="DetailsView1"Name="LOCATIONCODE"PropertyName="SelectedValue"/>

<asp:ControlParameterControlID="DetailsView1"Name="ADDRESS1"PropertyName="SelectedValue"/>

<asp:ControlParameterControlID="DetailsView1"Name="ADDRESS2"PropertyName="SelectedValue"/>

<asp:ControlParameterControlID="DetailsView1"Name="ADDRESS4"PropertyName="SelectedValue"/>

<asp:ControlParameterControlID="DetailsView1"Name="ADDRESS3"PropertyName="SelectedValue"/>

<asp:ControlParameterControlID="DetailsView1"Name="PROP_ID"PropertyName="SelectedValue"/>

<asp:ControlParameterControlID="DetailsView1"Name="ID"PropertyName="SelectedValue"/>

</UpdateParameters>

</asp:SqlDataSource>

|||

Why is every update parameter a ControlParameter? The parameters that are getting their values from being databound should be a plain "Parameter". Also what is the selectedvalue of detailsview1 in your test? 1?

Please change the parameters from type ControlParameter to type Parameter. If the problem still persists, then launch your app, go to the detailsview, enter into edit mode (So the dropdowns show), then post the HTML that one of the dropdowns that is bound to a field that is submitting a 1 by clicksing Tools->View Source in IE, and copying everything between the <SELECT and </SELECT> tags.

|||

I did what you said. It now no longer passing the null values as 1! Thank you!

However, it is not updating to the database even though I see the parameters updating. I am using the trace feature and the code below to trace the updating/updated parameters.

ProtectedSub Update_Updating(ByVal senderAsObject,ByVal eAs System.Web.UI.WebControls.SqlDataSourceCommandEventArgs)Handles Update.Updating

For xAsInteger = 0To e.Command.Parameters.Count - 1

Trace.Write(e.Command.Parameters(x).ParameterName)

Trace.Write(CStr(e.Command.Parameters(x).Value))

Next

Any Ideas?

EndSub

Protected Sub Update_Updated(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.SqlDataSourceStatusEventArgs) Handles Update.Updated

For x As Integer = 0 To e.Command.Parameters.Count - 1

Trace.Write(e.Command.Parameters(x).ParameterName)

Trace.Write(CStr(e.Command.Parameters(x).Value))

Next

End Sub

Conversion from type 'DBNull' to type 'String' is not valid.

Here is the code for the updated parameters

ProtectedSub Update_Updated(ByVal senderAsObject,ByVal eAs System.Web.UI.WebControls.SqlDataSourceStatusEventArgs)Handles Update.Updated

For xAsInteger = 0To e.Command.Parameters.Count - 1

Trace.Write(e.Command.Parameters(x).ParameterName)

Trace.Write(CStr(e.Command.Parameters(x).Value))

Next

EndSub

|||

oops sorry for the last post...let me repost again.

I did what you said. It now no longer passing the null values as 1! Thank you!

However, it is not updating to the database even though I see the parameters updating. I am using the trace feature and the code below to trace the updating/updated parameters.

Here's the code for updating parameters

Protected Sub Update_Updating(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.SqlDataSourceCommandEventArgs) Handles Update.Updating

For x As Integer = 0 To e.Command.Parameters.Count - 1

Trace.Write(e.Command.Parameters(x).ParameterName)

Trace.Write(CStr(e.Command.Parameters(x).Value))

Next

End Sub

Protected Sub Update_Updated(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.SqlDataSourceStatusEventArgs) Handles Update.Updated

For x As Integer = 0 To e.Command.Parameters.Count - 1

Trace.Write(e.Command.Parameters(x).ParameterName)

Trace.Write(CStr(e.Command.Parameters(x).Value))

Next

End Sub

Here is the code for the updated parameters

Protected Sub Update_Updated(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.SqlDataSourceStatusEventArgs) Handles Update.Updated

For x As Integer = 0 To e.Command.Parameters.Count - 1

Trace.Write(e.Command.Parameters(x).ParameterName)

Trace.Write(CStr(e.Command.Parameters(x).Value))

Next

End Sub

I got an error for the updated parameters code

Conversion from type 'DBNull' to type 'String' is not valid.

Any Ideas?

|||

Help?

It won't update the fields that currently have null values...

Help again? :)

|||Are you getting an error? Is it updating the fields that don't have null values?|||

Yes to both.

I am still having the error that I posted above and the update is also updating one of the columns that didn't have a null value with a null value.

Strangely, enough the update works if there are null values but it will still catch the exception of this error Conversion from type 'DBNull' to type 'String' is not valid when I step into the updated parameters. I would like to fix that so there won't be any problems in the future even though it still works. The another problem is that the update function will update this column named BLDG_SV with a null value even though it had a value, previously.

Thanks!!

|||Is the column named BLDG_SV or BUILDING_SV?|||yes sorry it is BUILDING_SV, forgive me.