Query with multiple ID to check and response...

Hi there, I tried to make it different as usual but i′m stacked into this problem:

Supose TABLE Details
ID_DET ID_CAR DETAILS
1 3 1,2,3,4,5,6
2 4 2,4
3 5 5,6,7,8

and
TABLE Details_Items
ID_DI DETAIL_NAME
1 Stereo HiFi CD
2 Alarm
3 AirConditioning
4 LeatherSeats
5 Pro Wires
6 Aluminium Wheels

The problem appears when i need to bring CAR DETAILS (NAME) from TABLE DETAIL_ITEMS.
Mi guess i that I should make something like:
SELECT * FROM DETAILS_NAME WHERE id_di = (( Array(i) FROM Details )) one by one...

I really dont know how to face it.

First I thought in bringing ALL details_Items (datafieldtext = id_di and datavaluetext=Details_names) into a dataview.
And then "somohow?" filter this dataview according with the Array previuosly splited by me with a For each function.

Then I thought "Perhaps" there is a simpliest way to do that using SQL views, o advanced SQL QUERYS.

and Finally I thought that creating a VIEW in for both TABLES would be great.

The point is that, neither 1,2,3 options, honestly , I dont know how to face them.

Thanks in advance, apologise my "rude" English grammar.

LUCAS ( From Argentina )

Are you saying that you are storing numeric values as a comma separated list in the DETAILS column? This is very bad database design. You should use a link table instead. Create a new table called DETAIL_IDS ?with two columns:?ID_DET and DETAIL_ID

eg.

?ID_DET ???DETAIL_ID
?1 ?????????????1
?1 ?????????????2
?1 ?????????????3
?1 ?????????????4
?1 ?????????????5
?1 ?????????????6
?2 ?????????????4
?2 ?????????????6

etc.|||

This is a very bad way to store IDs in string field because of speed and problems you have now. If you do not wont to change your database structure you can do something like this:

declare @.lcCommand as varchar(8000)

SELECT @.lcCommand = 'SELECT * FROM DETAILS_NAME WHERE id_di in (' + DETAILS + ') '
from Details
where ID_Car=3 -- for car with ID 3

Exec (@.lcCommand)

This is not recommended way to execute select statement but in your case is probably the simplest way. Another way would be to create table returned function which will convert comma delimited string to table and just select from this table. The advantage is that you will not build command string which is safest, and store procedures will be precompiled so works faster.

Thanks

JPazgier

sql

Query Tuning

Hi,
I have a query that selects 4 fields. One is of type Varchar(500). When I
execute the query, the response is about 9 seconds (very slow). When I
comment out the varchar field, it returns in less than 1 second.
It took me a while to figure out that it's not a missing index, i can't
figure this one out.
please advise.
rafaelHow many records are being returned?
"Rafael Chemtob" wrote:

> Hi,
> I have a query that selects 4 fields. One is of type Varchar(500). When
I
> execute the query, the response is about 9 seconds (very slow). When I
> comment out the varchar field, it returns in less than 1 second.
> It took me a while to figure out that it's not a missing index, i can't
> figure this one out.
> please advise.
> rafael
>
>|||10
"CBretana" <cbretana@.areteIndNOSPAM.com> wrote in message
news:6F062F1B-91A3-45B6-AB3E-F08DF4A37831@.microsoft.com...
> How many records are being returned?
> "Rafael Chemtob" wrote:
>
When I|||> How many records are being returned?
..And what is the average length of the data in those rows?
Thomas
"CBretana" <cbretana@.areteIndNOSPAM.com> wrote in message
news:6F062F1B-91A3-45B6-AB3E-F08DF4A37831@.microsoft.com...
> How many records are being returned?
> "Rafael Chemtob" wrote:
>|||ok, sorry for not being very detailed.
4 fields
id_rating INT
summary VARCHAR(500)
dt_rating smalldatetime
id_user INT
these are the 4 fields. The record count that's returned is 11 rows.
Hope that helps
thanks
"Thomas" <replyingroup@.anywhere.com> wrote in message
news:#45wbrTRFHA.2384@.tk2msftngp13.phx.gbl...
> ..And what is the average length of the data in those rows?
>
> Thomas
>
> "CBretana" <cbretana@.areteIndNOSPAM.com> wrote in message
> news:6F062F1B-91A3-45B6-AB3E-F08DF4A37831@.microsoft.com...
When I
>|||Is the performance discrepancy consistent? i.e., have you tested this a
number of times? How many records are in the table?,
and finally, is there an index on the table that contains all the other
three columns from the table, but not the varchar(500) column?
Also, please post the DDL for the tables, and the actual Query.
Charly
"Rafael Chemtob" wrote:

> ok, sorry for not being very detailed.
> 4 fields
> id_rating INT
> summary VARCHAR(500)
> dt_rating smalldatetime
> id_user INT
> these are the 4 fields. The record count that's returned is 11 rows.
> Hope that helps
> thanks
>
> "Thomas" <replyingroup@.anywhere.com> wrote in message
> news:#45wbrTRFHA.2384@.tk2msftngp13.phx.gbl...
> When I
>
>|||I mean, is there an index on the table which includes columns
(id_rating, dt_rating, id_user), but not Column summary ?
"Rafael Chemtob" wrote:

> ok, sorry for not being very detailed.
> 4 fields
> id_rating INT
> summary VARCHAR(500)
> dt_rating smalldatetime
> id_user INT
> these are the 4 fields. The record count that's returned is 11 rows.
> Hope that helps
> thanks
>
> "Thomas" <replyingroup@.anywhere.com> wrote in message
> news:#45wbrTRFHA.2384@.tk2msftngp13.phx.gbl...
> When I
>
>|||I query the table using id_rating (which is the PK).
and this is consistent. I comment out the varchar field and i get the
results MUCH quicker.
rafael
"CBretana" <cbretana@.areteIndNOSPAM.com> wrote in message
news:456CEB9F-6A05-464C-9EE5-93F4FF0B7DB3@.microsoft.com...
> I mean, is there an index on the table which includes columns
> (id_rating, dt_rating, id_user), but not Column summary ?
>
> "Rafael Chemtob" wrote:
>
When I
can't|||As I asked above, one possible reason for this is if there's an index that
includes the columns (id_rating, dt_rating, id_user), but NOT the summary
column. If that were the case, the query processor could use the index alon
e
for the query without Summary, but would be forced to do a table scan when
you include summary.. Is there such an index?
"Rafael Chemtob" wrote:

> I query the table using id_rating (which is the PK).
> and this is consistent. I comment out the varchar field and i get the
> results MUCH quicker.
> rafael
> "CBretana" <cbretana@.areteIndNOSPAM.com> wrote in message
> news:456CEB9F-6A05-464C-9EE5-93F4FF0B7DB3@.microsoft.com...
> When I
> can't
>
>|||Hi Rafael,
For performance questions like these, it is very important to post all
relevant DDL (so including indexes, constraints, etc.) and the exact
query.
So just a wild guess for now: make sure you have a clustered index on
the table. If the table does not have a clustered index, and you delete
many rows, then querying the table can become very slow.
HTH,
Gert-Jan
Rafael Chemtob wrote:
> Hi,
> I have a query that selects 4 fields. One is of type Varchar(500). When
I
> execute the query, the response is about 9 seconds (very slow). When I
> comment out the varchar field, it returns in less than 1 second.
> It took me a while to figure out that it's not a missing index, i can't
> figure this one out.
> please advise.
> rafael

Query Total Response Time

How can I know the total time (in miliseconds) for my SQL Server to answer a
query. I want to issue a query using an Query Analyzer and Measure the
Servers total response time. Thanks in advance!
On Wed, 2 Mar 2005 23:51:02 -0800, Borikoy wrote:

>How can I know the total time (in miliseconds) for my SQL Server to answer a
>query. I want to issue a query using an Query Analyzer and Measure the
>Servers total response time. Thanks in advance!
Hi Borikoy,
The simplest form is
SELECT CURRENT_TIMESTAMP
-- Insert your query here
SELECT CURRENT_TIMESTAMP
Slightly more advanced would be
DECLARE @.start datetime, @.end datetime
SET @.start = CURRENT_TIMESTAMP
-- Insert your query here
SET @.end = CURRENT_TIMESTAMP
SELECT DATEDIFF(ms, @.start, @.end)
In both cases, the time used to push the rows to the client is included.
For a clean measurement of time taken to process the query, I usually
change all SELECT queries to SELECT INTO queries (so that the ooutput
goes in a temp table instead of over a possibly slow network, or is
slowed by display processing).
Another way to get information about time taken is:
SET STATISTICS TIME ON
-- Insert your query here
SET STATISTICS TIME OFF
Check out the description of SET STATISTICS TIME in Books Online. And
you might wish to read up on SET STATISTICS IO as well.
Whatever way you use, you won't get accurate comparisons if you run
queries with data in the cache. Often, the first execution of a query
takes longer because data needs to be brought into cache from hard disk;
subsequent invocations go lots faster because the data is still in
cache, so the slow hard disk access is not necessary anymore. To clear
out the data cache and the procedure cache as well (so you won't reuse
old execution plans), run the following statements before doing your
tests:
CHECKPOINT -- Write dirty pages to disk
DBCC DROPCLEANBUFFERS -- Flush all non-dirty pages from cache
DBCC FREEPROCCACHE -- Remove all cached execution plans
Running the above statements on a production database will severely
impact the performance of your running processes - but I assume that you
don't intend to run performance tests on a live production database
anyway!
Best, Hugo
(Remove _NO_ and _SPAM_ to get my e-mail address)
sql

query to view current executing jobs

... I know i have asked this before and the response i got is run
sp_help_job.. Please bear with me as Im not a SQL guru . I would like to run
a script in QA and the output should give me the list of jobs that are
currently running. I have around 100 SQL Agent jobs on a server and instead
of refreshing my screen in EM to see the status of running, I want to see
those jobs only from within QA.
Can someone provide that query for me ? Would be highly appreciated.The procedure call is: exec msdb..sp_help_job
For each job, check the coding of current_execution_status:
0 Returns only those jobs that are not idle or suspended.
1 Executing.
2 Waiting for thread.
3 Between retries.
4 Idle.
5 Suspended.
7 Performing completion actions.
"Hassan" <fatima_ja@.hotmail.com> wrote in message
news:uO370WdGFHA.2784@.TK2MSFTNGP10.phx.gbl...
> .. I know i have asked this before and the response i got is run
> sp_help_job.. Please bear with me as Im not a SQL guru . I would like to
run
> a script in QA and the output should give me the list of jobs that are
> currently running. I have around 100 SQL Agent jobs on a server and
instead
> of refreshing my screen in EM to see the status of running, I want to see
> those jobs only from within QA.
> Can someone provide that query for me ? Would be highly appreciated.
>|||You could try the following query, instead of returning all the jobs, it
just returns current active jobs.
--find Jobs that are currently running:
exec msdb..sp_get_composite_job_info @.enabled=1 , @.execution_status = 1
"JohnnyAppleseed" <someone@.microsoft.com> wrote in message
news:uI581odGFHA.3376@.TK2MSFTNGP14.phx.gbl...
> The procedure call is: exec msdb..sp_help_job
> For each job, check the coding of current_execution_status:
> 0 Returns only those jobs that are not idle or suspended.
> 1 Executing.
> 2 Waiting for thread.
> 3 Between retries.
> 4 Idle.
> 5 Suspended.
> 7 Performing completion actions.
>
> "Hassan" <fatima_ja@.hotmail.com> wrote in message
> news:uO370WdGFHA.2784@.TK2MSFTNGP10.phx.gbl...
> run
> instead
see
>|||Thanks Britney
Do you know what I can use to find just failed jobs ?
"Britney" <britneychen_2001@.yahoo.com> wrote in message
news:ObkP23eGFHA.584@.TK2MSFTNGP14.phx.gbl...
> You could try the following query, instead of returning all the jobs, it
> just returns current active jobs.
>
> --find Jobs that are currently running:
>
> exec msdb..sp_get_composite_job_info @.enabled=1 , @.execution_status = 1
>
>
> "JohnnyAppleseed" <someone@.microsoft.com> wrote in message
> news:uI581odGFHA.3376@.TK2MSFTNGP14.phx.gbl...
to
> see
>|||This is the same too right
exec msdb..sp_help_job @.enabled=1 , @.execution_status = 1
"Britney" <britneychen_2001@.yahoo.com> wrote in message
news:ObkP23eGFHA.584@.TK2MSFTNGP14.phx.gbl...
> You could try the following query, instead of returning all the jobs, it
> just returns current active jobs.
>
> --find Jobs that are currently running:
>
> exec msdb..sp_get_composite_job_info @.enabled=1 , @.execution_status = 1
>
>
> "JohnnyAppleseed" <someone@.microsoft.com> wrote in message
> news:uI581odGFHA.3376@.TK2MSFTNGP14.phx.gbl...
to
> see
>