ambiguity performance problem

The first query execution time less than 1 second
But the second query takes around one minute

SELECT ACC_KEY1,ACC_STATUS_LAST FROM PSSIG.CLNT_ACCOUNTS INNER JOIN PSSIG.CLNT_CUSTOMERS ON
PSSIG.CLNT_ACCOUNTS.CSTMR_OID = PSSIG.CLNT_CUSTOMERS.CSTMR_OID
WHERE (PSSIG.CLNT_CUSTOMERS.CSTMR_START_DT >= '1900-1-1 12:00:00') AND
(PSSIG.CLNT_CUSTOMERS.CSTMR_END_DT <= '2106-12-31 12:00:00') AND
(PSSIG.CLNT_ACCOUNTS.ACC_KEY1 >= '0000000000000') AND
(PSSIG.CLNT_ACCOUNTS.ACC_KEY1 <= '9999999999999') AND
(PSSIG.CLNT_ACCOUNTS.ACC_STATUS_LAST in (5,-999)) AND
ACC_KEY1 > '0' ORDER BY ACC_KEY1

SELECT ACC_KEY1,ACC_STATUS_LAST FROM PSSIG.CLNT_ACCOUNTS INNER JOIN PSSIG.CLNT_CUSTOMERS ON
PSSIG.CLNT_ACCOUNTS.CSTMR_OID = PSSIG.CLNT_CUSTOMERS.CSTMR_OID
WHERE (PSSIG.CLNT_CUSTOMERS.CSTMR_START_DT >= '1900-1-1 12:00:00') AND
(PSSIG.CLNT_CUSTOMERS.CSTMR_END_DT <= '2106-12-31 12:00:00') AND
(PSSIG.CLNT_ACCOUNTS.ACC_KEY1 >= '0000000000000') AND
(PSSIG.CLNT_ACCOUNTS.ACC_KEY1 <= '9999999999999') AND
(PSSIG.CLNT_ACCOUNTS.ACC_STATUS_LAST = 5 ) AND
ACC_KEY1 > '0' ORDER BY ACC_KEY1

Note 1 : value -999 deos not exist in the table field ACC_STATUS_LAST
Note 2 : value 5 exist in most of rows about ( 999999/1000000 ) from the table rows count
Note 3 : the number of rows in each table around 15000000

Hi,

didi you have a look at the execution plan ? The execution plan can be seen in QA (using SQL 2k) or Query Pane (using SQL2k5) graphically or by using the statement SET SHOWPLAN ON before isseing the statement.

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de

|||Yeah, and then post it. I would love to see how an equality query is beat out by an OR query. This looks very much like it is probably an optimizer issue, unless the plan shows something obvious...|||

thank you for this notice ,

because there is a big difference between the access plan for both queries

and I don't now why this happen ,

Always show Subreport (need to always show Background Color)

I have tried adding a second dataset so that my subreport always returns a result, but this has not worked yet. I need a subreport to always show because the row is in Gainsboro background color and when there is no data, half the row is Gainsboro and half is White.

Any ideas?

make sure your query is returning a row, even if it is blank, or at least return a empty value if your result set is empty.

if mydataset.count (or whatever) =0 then

return " "

end if

you could check for this in your sql statement or query.

Always Display Subreport (including background color)

I have tried adding a second dataset so that my subreport always returns a
result, but this has not worked yet. I need a subreport to always shown
because the row is in Gainsboro background color and when there is no data,
half the row is Gainsboro and half is White.
Any ideas?I also do something like that too. I use a dataset called 'ForceRows' that
looks like this.
SELECT 'DummyRow'
Steve MunLeeuw
"bjkaledas" <bjkaledas@.discussions.microsoft.com> wrote in message
news:F2859D73-24D0-4F7D-96BD-A18B4B4BD4E7@.microsoft.com...
>I have tried adding a second dataset so that my subreport always returns a
> result, but this has not worked yet. I need a subreport to always shown
> because the row is in Gainsboro background color and when there is no
> data,
> half the row is Gainsboro and half is White.
> Any ideas?

Alternative to Creating View with Union in two databases?

I attempted to create a view in SQL Server 2000 that Unions two
queries. The first part of the query gets data from the local server,
the second part gets info from a linked server. (The query works fine
in Query Analyzer.)

I received this error when I tried to save the query:

ODBC error: [Microsoft][ODBC SQL Server Driver] The operation could
not be performed because the OLE DB provider 'SQLOLEDB' was unable to
begin a distributed transaction.

[Microsft][ODBC SQL Server Driver][SQL Server][OLE/DB provider
returned message: New transaction cannot enlist in the specified
transaction coordinator.]

After a little reading I discovered the "Database limitation":
"A view can be created on a table only in the database the view
creator is accessing".

That's my problem... is there a simple solution or alternative to
creating a view?

Thanks,
Mattmattd@.semiconductor.com (Matt D) wrote in news:7bd0381c.0310070503.4d416d94@.posting.google.c om:

> That's my problem... is there a simple solution or alternative to
> creating a view?

A stored procedure will likely do the trick.

--
Ross Presser -- rpresser AT imtek DOT com
... seeking a new quote ...|||Matt D (mattd@.semiconductor.com) writes:
> I attempted to create a view in SQL Server 2000 that Unions two
> queries. The first part of the query gets data from the local server,
> the second part gets info from a linked server. (The query works fine
> in Query Analyzer.)
> I received this error when I tried to save the query:

I had no problems to create a view spanning two servers.

Since you talk about saving query, I get the idea that you might
be using Enterprise Manager for the task. Try running the CREATE VIEW
statement from Query Analyzer instead.

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

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