Query performance problem

I'm having trouble figuring out why a query is having
varying response time. The following query takes anywhere
from a 2 seconds to 20 minutes:
select *
from tbl (nolock)
where ACTIVE = 1
There are 17 rows returned in the table. The largest data
in the text field is 34 KB, with most around 20 KB.
Anybody have any ideas?
Table structure is as follows:
CREATE TABLE [tbl] (
[ID] [int] IDENTITY (40, 1) NOT NULL ,
[ACTIVE] [bit] NOT NULL ,
[NAME] [varchar] (50) COLLATE NULL ,
[FILE] [text] NULL ,
CONSTRAINT [PK_tbl] PRIMARY KEY NONCLUSTERED
(
[SHEET_ID]
)
)What indexes do you have? And how many rows in the table? What query plan
does the optimizer select? Does it use an index on the Active column?
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
"Mark DeWaard" <anonymous@.discussions.microsoft.com> wrote in message
news:e84f01c40b99$728b2540$a101280a@.phx.gbl...
> I'm having trouble figuring out why a query is having
> varying response time. The following query takes anywhere
> from a 2 seconds to 20 minutes:
> select *
> from tbl (nolock)
> where ACTIVE = 1
> There are 17 rows returned in the table. The largest data
> in the text field is 34 KB, with most around 20 KB.
> Anybody have any ideas?
> Table structure is as follows:
> CREATE TABLE [tbl] (
> [ID] [int] IDENTITY (40, 1) NOT NULL ,
> [ACTIVE] [bit] NOT NULL ,
> [NAME] [varchar] (50) COLLATE NULL ,
> [FILE] [text] NULL ,
> CONSTRAINT [PK_tbl] PRIMARY KEY NONCLUSTERED
> (
> [SHEET_ID]
> )
> )|||There is only one index, The PK on the field "ID" (see DDL
below).
There are 21 rows in the table, the query returns 17.
Notice that there is a text column in the tbl (20-30KB).
The optimizer does a full tablescan on the table. Also
note, I am running a trace for that table and there is not
any update DML.
Thanks,
Mark

>--Original Message--
>What indexes do you have? And how many rows in the table?
What query plan
>does the optimizer select? Does it use an index on the
Active column?
>--
>Tibor Karaszi, SQL Server MVP
>http://www.karaszi.com/sqlserver/default.asp
>
>"Mark DeWaard" <anonymous@.discussions.microsoft.com>
wrote in message
>news:e84f01c40b99$728b2540$a101280a@.phx.gbl...
anywhere
data
>
>.
>|||OK. Well, you could try creating a supporting index for the query, but it
seems like the major issue here is actually accessing the BLOB data. Bit
still it is not so much data that I would suspect so varying response times.
And just to be certain, check for blocking (I know you mention you are
running a profiler trace, but just to be certain). I guess you could
investigate where the wait time is, a recent SQL Server Magazine had a nice
article regarding this.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
"Mark DeWaard" <mdewaard@.idtdna.com> wrote in message
news:b74201c40b9f$660907f0$a601280a@.phx.gbl...
> There is only one index, The PK on the field "ID" (see DDL
> below).
> There are 21 rows in the table, the query returns 17.
> Notice that there is a text column in the tbl (20-30KB).
> The optimizer does a full tablescan on the table. Also
> note, I am running a trace for that table and there is not
> any update DML.
> Thanks,
> Mark
>
> What query plan
> Active column?
> wrote in message
> anywhere
> data|||Have you attempted to call just the column names instead of using the "*"?
Andrew C. Madsen
Information Architect
Harley-Davidson Motor Company
"Mark DeWaard" <anonymous@.discussions.microsoft.com> wrote in message
news:e84f01c40b99$728b2540$a101280a@.phx.gbl...
> I'm having trouble figuring out why a query is having
> varying response time. The following query takes anywhere
> from a 2 seconds to 20 minutes:
> select *
> from tbl (nolock)
> where ACTIVE = 1
> There are 17 rows returned in the table. The largest data
> in the text field is 34 KB, with most around 20 KB.
> Anybody have any ideas?
> Table structure is as follows:
> CREATE TABLE [tbl] (
> [ID] [int] IDENTITY (40, 1) NOT NULL ,
> [ACTIVE] [bit] NOT NULL ,
> [NAME] [varchar] (50) COLLATE NULL ,
> [FILE] [text] NULL ,
> CONSTRAINT [PK_tbl] PRIMARY KEY NONCLUSTERED
> (
> [SHEET_ID]
> )
> )|||Another table has the "id" field as a fk. Could it be that
an exclusive lock is placed on the "id" when the other
table is inserted or updated?

>--Original Message--
>OK. Well, you could try creating a supporting index for
the query, but it
>seems like the major issue here is actually accessing the
BLOB data. Bit
>still it is not so much data that I would suspect so
varying response times.
>And just to be certain, check for blocking (I know you
mention you are
>running a profiler trace, but just to be certain). I
guess you could
>investigate where the wait time is, a recent SQL Server
Magazine had a nice
>article regarding this.
>--
>Tibor Karaszi, SQL Server MVP
>http://www.karaszi.com/sqlserver/default.asp
>
>"Mark DeWaard" <mdewaard@.idtdna.com> wrote in message
>news:b74201c40b9f$660907f0$a601280a@.phx.gbl...
DDL
not
table?
>
>.
>|||Yes, that is possible.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
"Mark DeWaard" <anonymous@.discussions.microsoft.com> wrote in message
news:c49201c40c6c$0fc53440$a601280a@.phx.gbl...
> Another table has the "id" field as a fk. Could it be that
> an exclusive lock is placed on the "id" when the other
> table is inserted or updated?
>
> the query, but it
> BLOB data. Bit
> varying response times.
> mention you are
> guess you could
> Magazine had a nice
> DDL
> not
> table?

Query Merging/ Query Transformation ??

Hello,

Can someone plz refer/recommend any document on query merging? I am working on a database sever. The response time, of view's query has become a challange to me. I have tried everything, the last hope left is query merging.
But I didnt find any docs/papers/books on it.

Plz help.
Shigs.
=============================
Are there those,
In this world of brave,
Who can tell me,
How should I behave,
When I am disgraced.
=============================Could you please explain on the term Query merging.

If you want to merge certain table in the query you can use JOINs, refer to books online for more information.|||Could you please explain on the term Query merging.

If you want to merge certain table in the query you can use JOINs, refer to books online for more information.|||Hi Satya

Thanks a lot. Query Merging that I referred was not related to merging of one table's data into another, but if I fire a query, which is containing a subquery in it, then the execution may take two different strategies, 1. Execute the subquery, fetch the result and compare this data with the parent querie's data. 2. Merge the subquery into Parent query and execute them as if the query wasn't subquery but a single level '0' query.

The best xample is Oracle's COMPLEX_QUERY_MERGING option. I want to understand, how Oarcle internally merges these queries? Is there any Optimizer Doc, which gives the detail?

Waiting...|||Is your question in regards to Oracle? This is a SQL Server forum, and SQL Server generally does a good job of optimizing queries, even with complex embedded subqueries.
If you are having slow response times on SQL Server, then post your query and someone on the forum might be able to help you optimize it.|||You can check the execution plan of that query in query analyzer which gives you better idea about the table scans and performance.|||I have a question...are there fewer and fewer Oracle projects out there these days?|||One can only hope...