Query Problem

Hi,
I'm having a problem with a query... Asume I have the one table with two
columns
Col1 Col2
Mark 3
Clara 5
Bob 7
Clara 5
Clara 8
What I whant is to get the value of the most repeated entry in Col1 (in this
case, Clara)... any ideas?
ThanksHere's one way:
SELECT TOP 1 WITH TIES Col1
FROM Tbl
GROUP BY Col1
ORDER BY COUNT(*) DESC
... Note I added WITH TIES -- It is possible that there can be multiple
values with the same number of rows, so you need to decide how you want to
handle that situation.
--
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
--
"Alejandro" <Alejandro@.discussions.microsoft.com> wrote in message
news:6749A743-6C80-4A1E-8393-113079F1F8C7@.microsoft.com...
> Hi,
> I'm having a problem with a query... Asume I have the one table with two
> columns
> Col1 Col2
> Mark 3
> Clara 5
> Bob 7
> Clara 5
> Clara 8
> What I whant is to get the value of the most repeated entry in Col1 (in
this
> case, Clara)... any ideas?
> Thanks|||Thanks... It works fine...
"Alejandro" wrote:
> Hi,
> I'm having a problem with a query... Asume I have the one table with two
> columns
> Col1 Col2
> Mark 3
> Clara 5
> Bob 7
> Clara 5
> Clara 8
> What I whant is to get the value of the most repeated entry in Col1 (in this
> case, Clara)... any ideas?
> Thanks

Query plan utilization vs CPU time...

I was tuning a query testing out SARG with these two queries:

select col1 from table1 (nolock) where col1 like '#,%ABC%' or col1 like 'BC,%ABC%'
select col1 from table1 (nolock) where col1 like '%ABC%'

I flushed out the cache, added an index to col1, then ran those two together. Provided below are the actual query plan and stat time:

Query 1: Query cost (relative to the batch): 0%
select col1 from table1 (nolock) where col1 like '#,%ABC%' or col1 like 'BC,%ABC%'
-
SELECT Index Seek
Cost:0% <- [DB1].[dbo].[table1].[Idx..]

Cost: 100%

Query 2: Query cost (relative to the batch): 100%
select col1 from table1 (nolock) where col1 like '%ABC%'
-
SELECT Index Scan
Cost:0% <- [DB1].[dbo].[table1].[Idx..]

Cost: 100%

STAT TIME--
SQL Server parse and compile time:
CPU time = 0 ms, elapsed time = 1 ms.

SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 1 ms.
SQL Server parse and compile time:
CPU time = 0 ms, elapsed time = 7 ms.

(3 row(s) affected)
Table 'table1'. Scan count 2, logical reads 2932, physical reads 0, read-ahead reads 18, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

(1 row(s) affected)

SQL Server Execution Times:
CPU time = 938 ms, elapsed time = 943 ms.

(3 row(s) affected)
Table 'table1'. Scan count 1, logical reads 2927, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

(1 row(s) affected)

SQL Server Execution Times:
CPU time = 515 ms, elapsed time = 505 ms.
SQL Server parse and compile time:
CPU time = 0 ms, elapsed time = 1 ms.

SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 1 ms.

STAT TIME--

As expected, SARGable Query 1 did a nonclustered index seek and nonSARGable Query 2 did an index scan instead. According to the query plan, Query 1 consumed 0% relative to the batch whereas Query 2 is 100%. When I checked the CPU time, I was a bit confused because Query 1 showed CPU time of 938ms whereas Query 2 showed 515ms. I triple checked and every time I got similar results. I am sure I'm missing something, could someone please tell me what I'm missing? Thanks a bunch!

Btw, I'm using SQL Server 2005. Any suggestion please, even just tell me if this is the right place to post this question. Hope some of you know the answer to this. Thanks!

Query plan utilization vs CPU time...

I was tuning a query testing out SARG with these two queries:

select col1 from table1 (nolock) where col1 like '#,%ABC%' or col1 like 'BC,%ABC%'
select col1 from table1 (nolock) where col1 like '%ABC%'

I flushed out the cache, added an index to col1, then ran those two together. Provided below are the actual query plan and stat time:

Query 1: Query cost (relative to the batch): 0%
select col1 from table1 (nolock) where col1 like '#,%ABC%' or col1 like 'BC,%ABC%'
-
SELECT Index Seek
Cost:0% <- [DB1].[dbo].[table1].[Idx..]

Cost: 100%

Query 2: Query cost (relative to the batch): 100%
select col1 from table1 (nolock) where col1 like '%ABC%'
-
SELECT Index Scan
Cost:0% <- [DB1].[dbo].[table1].[Idx..]

Cost: 100%

STAT TIME--
SQL Server parse and compile time:
CPU time = 0 ms, elapsed time = 1 ms.

SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 1 ms.
SQL Server parse and compile time:
CPU time = 0 ms, elapsed time = 7 ms.

(3 row(s) affected)
Table 'table1'. Scan count 2, logical reads 2932, physical reads 0, read-ahead reads 18, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

(1 row(s) affected)

SQL Server Execution Times:
CPU time = 938 ms, elapsed time = 943 ms.

(3 row(s) affected)
Table 'table1'. Scan count 1, logical reads 2927, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

(1 row(s) affected)

SQL Server Execution Times:
CPU time = 515 ms, elapsed time = 505 ms.
SQL Server parse and compile time:
CPU time = 0 ms, elapsed time = 1 ms.

SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 1 ms.

STAT TIME--

As expected, SARGable Query 1 did a nonclustered index seek and nonSARGable Query 2 did an index scan instead. According to the query plan, Query 1 consumed 0% relative to the batch whereas Query 2 is 100%. When I checked the CPU time, I was a bit confused because Query 1 showed CPU time of 938ms whereas Query 2 showed 515ms. I triple checked and every time I got similar results. I am sure I'm missing something, could someone please tell me what I'm missing? Thanks a bunch!

Btw, I'm using SQL Server 2005. Any suggestion please, even just tell me if this is the right place to post this question. Hope some of you know the answer to this. Thanks!
sql

Query performance

The below query seems to be very slow :
select distinct a.* from test a inner join test1 b on b.col1 = a.col1 inner join test2 c on c.col2 = a.col2 where exists (select NULL from test3 d where (d.col3 = a.col3 or a.col3 is null))
All the columns involved in the WHERE clause and JOIN conditions have index. Is there any alternative available for the above which can increase the performance ?
Please advice,
Thanks,
Smitha

The first thing you need to do is get rid of the "distinct" keyword - that alone will slow things down considerably. If you have duplicate rows in your table then you should remove them. If the number of occurances of a duplicate row is important to your application, then add that as a column, and eliminate the duplicates.

The other thing it'd seem to me to help, although I haven't tested it, would be to replace your WHERE EXISTS clause with an outer join with test3 on d.col3=a.col3. I don't know your data though, so I'm not sure of the effect of that change.

|||

How slow is 'very slow'...?
How much data is there involved in the tables?
What does the query plan look like?
Which table or tables shows the largest amount of data that's been worked on?

For an alternative, you need to provide some sample data along with the desired result and a brief explanation of what the query is supposed to do.

/Kenneth

|||The other thing you would have to get rid of is the a.*, retrieve only the columns you really need.

Jose Luis
|||

What does the query plan look like?

First are you sure you need to do a DISTINCT?

See these links for some overview

http://www.sql-server-performance.com/transact_sql.asp

http://www.sql-server-performance.com/nb_select_distinct.asp

sql