How to make you reports load faster ?

Any ideas how to make reports faster when returning lots fo rows?
I know you would need to work on your sql query etc..
Or maybe cache it.

But i'm thinking of having a kind of middle tier thing that sits between your sql database and the reports itself.

Any ideas would be appreciated

Dear rote,

Caching is certainly one option. Search the sql books online for Report Caching, Execution Snapshots and Report History.

If possible, keep your report server on a different machine. Adding memory will help increase performance.

http://www.microsoft.com/technet/prodtechnol/sql/2005/pspsqlrs.mspx#E6NAC

HTH,
Suprotim Agarwal

--
http://www.dotnetcurry.com
--

|||

RS comes with two middle tier elements of its own: snapshots and caching <s>. Do either of these meet your needs?

>L<

How to make this simple SELECT query perform faster

Hi,

I have the following simple SELECT query:

SELECT * FROM TRX_LAPD

But the problem that table TRX_LAPD is very big. Althoug, I am using this query in a Network envirenment.

If I implement this query inside Microsoft SQL Server, the first time is somehow slow (40 sec), but becasue of the caching cabability the next time is very fast.

The thing that I am using this query in Visual Web Developer, so to be accessed by other users in the local network, but it seems that there is no caching features; each time I execute the query the implementation remains slow (40 sec) even from the from the server PC which have the SQL Server running.

I used also a simple Stored Procedure, but nothing changed

Below my code in Visual Web Developer with VB script:

Sub getmytable()
Dim sql As String
sql = "SELECT * FROM TRX_LAPD"
'or sql = "EXEC getTRX_LAPD" if I will use the Stored Procedure

MySqlDataSource.SelectCommand = sql
'where MySQLDataSource is an SqlDataSource control
End Sub

and the Stored Procedure that I tried also:

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

ALTER PROCEDURE [dbo].[getTRX_LAPD]
AS
BEGIN
SET NOCOUNT ON;
SELECT * FROM TRX_LAPD
END

The SqlDatasource is bounded to a GridView control
I really appreciate any help.
Thanks

There are two basic rules of querying a database table:

1. Query only the rows that you need.
2. Query only the columns that you need.

By doing "SELECT * FROM TRX_LAPD", you are retreiving all the rows and all the columns all the time. Is that really a necessity? If you are doing this on a "very big" table (as you as say) it is bound to take time. I suggest you should revisit the design of querying the entire table every time.

how to make this query?

Hello,

I have a table structure like this where TB1 ... TD9 represent pks and fks

Now, I would like to know for TABLEA - TA1, how many TDs exist. Similarily for any other primary key in TableA. Can we write a query to do that? Shall appreciate your ideas.

Thanks in advance.

TABLEA TA1 TB1
TB2
TABLEB TB1 TC1
TC2
TB2 TC3
TC4

TABLED TC1 TD2
TD3
TC2 TD4
TD5
TC3 TD6
TD7
TC4 TD8Hello,

I have a table structure like this where TB1 ... TD9 represent pks and fks

Now, I would like to know for TABLEA - TA1, how many TDs exist. Similarily for any other primary key in TableA. Can we write a query to do that? Shall appreciate your ideas.

Thanks in advance.

TABLEA TA1 TB1
TB2
TABLEB TB1 TC1
TC2
TB2 TC3
TC4

TABLED TC1 TD2
TD3
TC2 TD4
TD5
TC3 TD6
TD7
TC4 TD8|||Sounds like you just want to do this:

select ta1, count(*)
from tablea
group by ta1;

I don't understand this part of your question though: "Similarily for any other primary key in TableA", because a table only has one primary key, and a primary key value will always appear in exactly one row.|||In table a TA1 has TB1 and TB2 as foreign keys.
TB1 has TC1 and TC2. TC1 has TD2 TD3. This grows like a tree ....

Now, for TA1, the query should output TD2 .. TD8

That means for a given key of first table, we have to get all the keys of last table (foreign keys that exist in last but one table)|||A simple query is below. (This was just to get the results)

One expected / correct method to achieve this is to use a PL/SQL procedure that implements recursion. I would also recommend having a single table containing the PK-FK relationships.

tableA

C1 C2
---- ----
TA1 TB1
TA1 TB2

TableB and TableC follow the same structure

SQL> select tableC.c2
2 from tableA, tableB, tableC
3 where tableA.c2 = tableB.c1 AND
4 tableC.c1 = tableB.c2 AND
5 tableA.c1 = 'TA1';

C2
----
TD2
..
TD8sql

How to make this query...

Hi all. I have tried making this query for a while and i didn't manage to.

I know it is something related to JOIN, but i didn't manage to make it work :\

I have two tables.

Table1- db_stockprices

Table2- db_stockSymbols

db_stockprices is getting updated every day with stock daily prices.

db_stockprices looks like:

ID, SymbolID, Price,Date

1 1 33.21 1/1/2007

2 1 33.14 1/2/2007

3 1 34.21 1/3/2007

4 2 11.42 1/1/2007

5 2 11.73 1/2/2007

6 3 18.32 1/1/2007

7 3 19.11 1/2/2007

8 3 18.41 1/3/2007

9 4 52.44 1/1/2007

and so on for all the stocks.

ID is Primary Key

SymbolID is related to SymbolID in db_stockSymbols which contains all the stock information.

What i am trying to do is an SQL QUERY that will delete all same SymbolID Rows if a specific INPUT date do not exist within that SymbolID.

Example:

If the INPUT date for the query is 1/3/2007 all rows with SymbolID "2" abd "4" will be deleted since 1/3/2007 does not exist in SymbolID "2" and "4". Moreover, the row in db_stockSymbols with SymbolID "2" and "4" will also be deleted.

Is this possible within 1 SQL Query?

I would really appreciate a good query example for this example..

I'm away from a SQL Server computer and i did not test the following but try it on a test table:

delete from db_stockprices

where symbolid in (select Symbolid from db_stockprices where Date<>@.yourdatavalue)

hth

|||Hi ggciubuc

This wont do it right.. This query will delete the whole table.

Code Snippet

select Symbolid from db_stockprices where Date<>@.yourdatavalue

this will select all symbolIDs that does not have @.yourdatevalue which is most cases is the whole SynbolIDs..

Am i correct?

|||

No, let's examine the select I proposed :

delete from db_stockprices

where symbolid in (select Symbolid from db_stockprices where Date<>@.yourdatavalue)

let's say

"select Symbolid from db_stockprices where Date<>'1/3/2007' " return "2" and "4" so then select for deleting will be transformed in

delete from db_stockprices where symbolid in ("2","4")

so will be deleted all rows you desired.

So, this solution I think will work.

|||

I see what you mean.

I thought by writing "select Symbolid from db_stockprices where Date<>'1/3/2007' "

it will also return SymbolID "1" and "3" because "1" and "3" contains dates which are different from "1/3/2007" (the 1/2/2007 and the 1/1/2007)

ID, SymbolID, Price,Date

1 1 33.21 1/1/2007

2 1 33.14 1/2/2007

3 1 34.21 1/3/2007

4 2 11.42 1/1/2007

5 2 11.73 1/2/2007

6 3 18.32 1/1/2007

7 3 19.11 1/2/2007

8 3 18.41 1/3/2007

9 4 52.44 1/1/2007

|||

Hi Folks,

Unfortunately, the answer Gigi gave won't work. The problem is that

select Symbolid from db_stockprices where Date<>@.yourdatavalue

will return the symbols from all rows whose dates don't match your date. I.e., if a stock has a price row on a date other than your date, then it will be returned. The only symbols that won't be returned will be those that only had a price for the date entered.

You can build up a query that will do what you want. There may be other, better ways to do this, but here's one way to think through the problem. Start by selecting all symbols that do have a trade on the date you want:

SELECT SymbolID FROM db_stockprices WHERE Date = @.date

If @.date is 1/3/2007, this will return the symbols 1 and 3. It may seem counterintutive to do this, but now you can find all symbols that are not in this list:

SELECT SymbolID FROM db_stockprices WHERE SymbolID NOT IN (

SELECT SymbolID FROM db_stockprices WHERE Date = @.date

)

Again, with @.date = 1/3/2007, this will return symbols 2 and 4. Now you can build the delete:

DELETE FROM db_symbols WHERE SymbolID IN (

SELECT SymbolID FROM db_stockprices WHERE SymbolID NOT IN (

SELECT SymbolID FROM db_stockprices WHERE Date = @.date

)

)

Hope this helps!

-Isaac

|||

Yes Isaac but to have a full answer for this post raindm have to associate DELETE CASCADE option to the relationship between db_stockSymbols and db_stockprices; in this way all rows from db_stockprices will be deleted and deleteing operation can be made in 1 SQL.

hth

|||

Gigi,

Good pointI missed that from the original post.

Cheers,

-Isaac

|||

Hi, now it makes more sense

About the " DELETE CASCADE " I do it from SQL Server Management (2005) ? If so where exactly?

|||

I found where to change the "Delete Cascade"

In db_stockprices i select SymbolID (its a forien key) and press relationship. I have a relationship i already created -"FK_db_stockprices_db_stockSymbols" . there i have INSERT and UPDATE specifications.. I see the options inside "INSERT and UPDATE specifications.." and i see DELETE RULE and there is "CASCADE". Is this correct? does it effect each other no matter from what table i delete a row?

I understand I can use "Update rule" so every update to the SymbolID in table1 will affect table2 and the opposite?

|||When you delete a row in table1 that supply foreign key for table2 the correspondent rows in table2 will be deleted (DELETE CASCADE) when modify the key in the table1 the values for foreign keys in table2 will be modified too (UPDATE CASCADE).|||

Okey,

thanks a lot you guys.

How to make the database execute query faster?

i got a very big problem help i need my database to execute one query in les
s
than a min but it executes for 8 mins is there any way i can shorten the
period of time execution?Optimize the schema, the query, and then some.
Impossible to say without seeing DDL and sample data. A general description
of the server might also help.
Read this for clues:
http://www.aspfaq.com/etiquette.asp?id=5006
ML
http://milambda.blogspot.com/

How to make the database execute query faster PLEASE HELP

i got a very big problem help i need my database to execute one query in les
s
than a min but it executes for 8 mins is there any way i can shorten the
period of time execution?
this is the codei am excuting some of the tables have 1-3gb big in size
SELECT DISTINCT OmimVarLocusIdSNP.snp_id,Gene.Gene_Name,
b125_SNPContigLoc_35_1.phys_pos,b125_SNPContigLoc_35_1.rc_ngbr,
b125_SNPContigLoc_35_1.allele,
b125_SNPContigLocusId_35_1.contig_acc,SNPSubSNPLink.subsnp_id,
SubSNPSeq3_ins.line_num,SubSNPSeq3_ins.line3,SubSNPSeq5_ins.line_num,
SubSNPSeq5_ins.line5,b125_ContigInfo_35_1.group_term,
b125_ContigInfo_35_1.group_label,b125_ContigInfo_35_1.contig_label,
AlleleFreqBySsPop.source,AlleleFreqBySsPop.freq,
SubPop.samplesize
From tx
INNER JOIN OmimVarLocusIdSNP
ON tx.Omim_No=OmimVarLocusIdSNP.omim_id
INNER JOIN Gene
ON tx.Omim_No=Gene.Omim_No
INNER JOIN b125_SNPContigLoc_35_1
ON OmimVarLocusIdSNP.snp_id=b125_SNPContigLoc_35_1.snp_id
INNER JOIN b125_SNPContigLocusId_35_1
ON OmimVarLocusIdSNP.snp_id=b125_SNPContigLocusId_35_1.snp_id
INNER JOIN SNPSubSNPLink
ON OmimVarLocusIdSNP.snp_id=SNPSubSNPLink.snp_id
INNER JOIN AlleleFreqBySsPop
ON SNPSubSNPLink.subsnp_id=AlleleFreqBySsPop.subsnp_id
INNER JOIN SubPop
ON SNPSubSNPLink.subsnp_id=SubPop.subsnp_id
INNER JOIN SubSNPSeq3_ins
ON SNPSubSNPLink.subsnp_id=SubSNPSeq3_ins.subsnp_id
INNER JOIN SubSNPSeq5_ins
ON SNPSubSNPLink.subsnp_id=SubSNPSeq5_ins.subsnp_id
INNER JOIN b125_ContigInfo_35_1
ON b125_SNPContigLocusId_35_1.contig_acc=b125_ContigInfo_35_1.contig_acc
WHERE
tx.Omim_Text LIKE '%LIVER%'
ORDER BY snp_id ASCConsider using full-text search (FTS). What data type is the Omim_Text
column? If it's one of the larger types (text, ntext, varchar(>900)) using
FTS will improve execution of such queries.
ML
http://milambda.blogspot.com/|||Apart from database tuning, have you set up and tuned your server
aappropriately? Are databases on a seperate drive from log files? Are
they seperate from temp directories? Do you have enough memeory and
CPU?
While it's true that most queries are sluggish becuase of the way that
they are written, a finely tunned server can add a lot to the
performance.
What's the bottlenexck in this particular query? Is it the DISTINCT,
or is it not taking advantage of indexes?|||I think its because of the like operator.
Just to make sure.
Can you run this query and find out how long it takes?
select * from
tx.Omim_Text LIKE '%LIVER%'
and what is this table tx. how many rows does it have?
And just a comment on ML's post. Fulltext indexing will help if number of
rows is in the order of a few millions and moreover, since the index is
seperately stored outside SQL, it will use lots of I/O in the process. So I
would suggest we go for FTS only if all else fails.|||out of interest, how long does:
SELECT * FROM tx WHERE Omim_text LIKE '%LIVER%' take to return, and how
many rows do you get back?
if this is the bottleneck then as ML said, full-text search could help.
Otherwise it looks like indexes on your joins would be helpful.
If only there was a way to post an execution plan...|||Firstly, have you checked the execution plan to make sure the tables
are procesed in the order you expect and the correct indexes have been
used?
Secondly, how big is the tx table? Is the fact that it's doing a table
scan on this table the reason it's slow? Or is it joining onto one of
the other tables that's slow?
As other posters have said, simplify the query to just "select ...
from tx where ..." to see if that's slow. If it is then there's
probably not a lot you can do (apart from maybe a full-text search,
however I have never done this myself). If that's fast then keep adding
tables to the query until you can pinpoint when it slows down.
Kris|||Include DDL, indexes, primary keys and foreign keys, a description of the
business issue (do you really need all these tables?), and how many rows you
actually expect to be returned.
"Fairy239" <Fairy239@.discussions.microsoft.com> wrote in message
news:D38E93C4-8C58-443B-BDD1-034F1C783B12@.microsoft.com...
> i got a very big problem help i need my database to execute one query in
less
> than a min but it executes for 8 mins is there any way i can shorten the
> period of time execution?
> this is the codei am excuting some of the tables have 1-3gb big in size
> SELECT DISTINCT OmimVarLocusIdSNP.snp_id,Gene.Gene_Name,
> b125_SNPContigLoc_35_1.phys_pos,b125_SNPContigLoc_35_1.rc_ngbr,
> b125_SNPContigLoc_35_1.allele,
> b125_SNPContigLocusId_35_1.contig_acc,SNPSubSNPLink.subsnp_id,
> SubSNPSeq3_ins.line_num,SubSNPSeq3_ins.line3,SubSNPSeq5_ins.line_num,
> SubSNPSeq5_ins.line5,b125_ContigInfo_35_1.group_term,
> b125_ContigInfo_35_1.group_label,b125_ContigInfo_35_1.contig_label,
> AlleleFreqBySsPop.source,AlleleFreqBySsPop.freq,
> SubPop.samplesize
>
> From tx
> INNER JOIN OmimVarLocusIdSNP
> ON tx.Omim_No=OmimVarLocusIdSNP.omim_id
> INNER JOIN Gene
> ON tx.Omim_No=Gene.Omim_No
> INNER JOIN b125_SNPContigLoc_35_1
> ON OmimVarLocusIdSNP.snp_id=b125_SNPContigLoc_35_1.snp_id
> INNER JOIN b125_SNPContigLocusId_35_1
> ON OmimVarLocusIdSNP.snp_id=b125_SNPContigLocusId_35_1.snp_id
> INNER JOIN SNPSubSNPLink
> ON OmimVarLocusIdSNP.snp_id=SNPSubSNPLink.snp_id
> INNER JOIN AlleleFreqBySsPop
> ON SNPSubSNPLink.subsnp_id=AlleleFreqBySsPop.subsnp_id
> INNER JOIN SubPop
> ON SNPSubSNPLink.subsnp_id=SubPop.subsnp_id
> INNER JOIN SubSNPSeq3_ins
> ON SNPSubSNPLink.subsnp_id=SubSNPSeq3_ins.subsnp_id
> INNER JOIN SubSNPSeq5_ins
> ON SNPSubSNPLink.subsnp_id=SubSNPSeq5_ins.subsnp_id
> INNER JOIN b125_ContigInfo_35_1
> ON b125_SNPContigLocusId_35_1.contig_acc=b125_ContigInfo_35_1.contig_acc
> WHERE
> tx.Omim_Text LIKE '%LIVER%'
>
> ORDER BY snp_id ASC
>|||I need a total a total of 17columns btw i am using the sql 2005 version.
ifthere is any way to tune the sql please teach me how to
.
tx=56.6mb ,b125_SNPContigLoc_35_1=2.91gb,
b125_SNPContigLocusId_35_1=854mb,SNPSubS
NPLink=147mb,
SubSNPSeq3_ins=1.87gb,SubSNPSeq5_ins=1.87gb,
b125_ContigInfo_35_1=590kb,AlleleFreqByS
sPop=899mb,SubPop=117mb
I feel it is the join of the tables that makes it slow as the files are very
big
i tried doins the first 2 columns they took me
i tried doing this and it took me 16sec.
SELECT DISTINCT OmimVarLocusIdSNP.snp_id,Gene.Gene_Name,
From tx
INNER JOIN OmimVarLocusIdSNP
ON tx.Omim_No=OmimVarLocusIdSNP.omim_id
INNER JOIN Gene
ON tx.Omim_No=Gene.Omim_No
WHERE
tx.Omim_Text LIKE '%LIVER%'
ORDER BY snp_id ASC
----
--
but when i add one table and three columns it took me 2min24sec
SELECT DISTINCT OmimVarLocusIdSNP.snp_id,Gene.Gene_Name,
b125_SNPContigLoc_35_1.phys_pos,b125_SNPContigLoc_35_1.rc_ngbr,
b125_SNPContigLoc_35_1.allele,
From tx
INNER JOIN OmimVarLocusIdSNP
ON tx.Omim_No=OmimVarLocusIdSNP.omim_id
INNER JOIN Gene
ON tx.Omim_No=Gene.Omim_No
INNER JOIN b125_SNPContigLoc_35_1
ON OmimVarLocusIdSNP.snp_id=b125_SNPContigLoc_35_1.snp_id
WHERE
tx.Omim_Text LIKE '%LIVER%'
ORDER BY snp_id ASC
"Jim Underwood" wrote:

> Include DDL, indexes, primary keys and foreign keys, a description of the
> business issue (do you really need all these tables?), and how many rows y
ou
> actually expect to be returned.
>
> "Fairy239" <Fairy239@.discussions.microsoft.com> wrote in message
> news:D38E93C4-8C58-443B-BDD1-034F1C783B12@.microsoft.com...
> less
>
>|||The data type is text .Omim_text is one column has a lot of words
"ML" wrote:

> Consider using full-text search (FTS). What data type is the Omim_Text
> column? If it's one of the larger types (text, ntext, varchar(>900)) using
> FTS will improve execution of such queries.
>
> ML
> --
> http://milambda.blogspot.com/|||Fairy239 wrote:
> I feel it is the join of the tables that makes it slow as the files are ve
ry
> big
Try turning FORCEPLAN on to force the optimiser to process the tables
in order you have them in the query (note: make sure you have the order
correct first!). And possibly you could force indexes.
e.g.
SET FORCEPLAN ON
SELECT DISTINCT OmimVarLocusIdSNP.snp_id,Gene.Gene_Name,
b125_SNPContigLoc_35_1.phys_pos,b125_SNPContigLoc_35_1.rc_ngbr,
b125_SNPContigLoc_35_1.allele,
>From tx
INNER JOIN OmimVarLocusIdSNP WITH (INDEX(xxxxxxx))
ON tx.Omim_No=OmimVarLocusIdSNP.omim_id
INNER JOIN Gene WITH (INDEX(xxxxxxx))
ON tx.Omim_No=Gene.Omim_No
INNER JOIN b125_SNPContigLoc_35_1 WITH (INDEX(xxxxxxx))
ON OmimVarLocusIdSNP.snp_id=b125_SNPContigLoc_35_1.snp_id
WHERE tx.Omim_Text LIKE '%LIVER%'
ORDER BY snp_id ASC
SET FORCEPLAN OFF
Others will tell you that you shouldn't have to use FORCEPLAN but in my
experience the optimiser often gets it wrong for queries with large
tables.
Kris

How to make table relations using query.

.

Hi,

How can I make relations between two tables using query? Tables are already there with data.

RegardsDo you mean you want to add a Foreign Key constraint?

Or do you just want to select the data?

To select the data, you would use a JOIN:

SELECT
 Table1.name,
 Table2.address
FROM
 Table1
INNER JOIN
 Table2 ON Table1.ID = Table2.ID

If you give us some more information we should be able to provide better help.

Terri|||Hi,

Thanks. Actually I uploaded my SQl Server DB to a free server for testing purpose. Tables were uploaded successfully but the relationship between the tables were erased, I mean Primary key and Foriegn key. The problem is that they have only a web based panel working to update database and I have to use Query Commands to manage my DB.

Tables are already there, I just need to make relations (Primary & Foreign Kies) between these tables.

I hope I could make it a little bit clear.

Regards,|||You would need something along these lines (assuming column names for myPKColumn and myFKColumn):

ALTER TABLE
 myTable
ADD CONSTRAINT
 myPKConstraintName
PRIMARY KEY (myPKColumn)
ALTER TABLE
 myTable
ADD CONSTRAINT
 myFKConstraintName
FOREIGN KEY (myFKColumn)

See BOL orConstrain your SQL Server data with T-SQL for more info.

Terri

how to make select query to access view in other server

Dear All,

i am making small web application using asp.net, C# ,sql2000.

i want a about regarding how to access view or table from other server to local server. i have base database where there is a view which need to access in my database of local server.
that is how to make select query to access view in other server

Please help

thanks

You have to add you second server to linked servers (server objects-> linked servers-> right click new linked server) and next in your query use server name in table name like:

select * from [server].[database].dbo.[tablename]

or use open query

Select * from OPENQUERY([servername],'SELECT * from [database].dbo.[tablename]')

second is faster but use it only if your query will hit only data on second server.

Thanks

sql

how to make select query to access view in other server

Dear All,

i am making small web application using asp.net, C# ,sql2000.

i want a about regarding how to access view or table from other server to local server. i have base database where there is a view which need to access in my database of local server.
that is how to make select query to access view in other server
Please help

thanks

there are many ways to access other server.database.table

(a) Configure LinkedServer

b)OPENDATASOURCE

(c) OpenQuery

read about this in BOL

Madhu

how to make query run faster?

hello i have two queries that i need to combine.... the first queryexecutes within one second and the second query executes within 7 seconds.... I used UNION to join this two query.. but my problem is that when using UNION my query now executes within 3:00 minutes?... how can i make my query run faster?... thanks so much....

here's my query:

SELECT d.question_section_name, d.question_sort_order, d.question_number,
d.question_text, d.Range, d.answer, d.Points, d.audit_id, d.question_type,
d.weighted, d.na, d.pf, d.nw, d.wh_survey_id, d.wh_question_id,
d.audit_date, d.survey_name, d.user_name, d.user_email, d.location_id,
b.question_section_name as question_section_name_2, b.rating,
b.survey_threshold_success, b.survey_threshold_failure, b.survey_threshold,
b.maximum_points, b.scored_points, b.percent_scored_of_max,
b.percent_total_score
FROM vw_audit_detail_report d JOIN (SELECT TOP 1 * FROM
vw_audit_detail_report_summary e
WHERE (e.location_id = 4919
AND (e.audit_date BETWEEN '2003-07-11' AND '2003-07-11'))) b
ON d.audit_id = b.audit_id and (d.question_section_name <>
b.question_section_name)
WHERE (d.location_id = 4919
AND (d.audit_date BETWEEN '2003-07-11' AND '2003-07-11'))
And d.question_section_name NOT IN (SELECT question_section_name
FROM vw_audit_detail_report_summary f where
(f.location_id = 4919
AND (f.audit_date BETWEEN '2003-07-11' AND '2003-07-11')))

UNION

SELECT a.question_section_name, a.question_sort_order, a.question_number,
a.question_text, a.Range, a.answer, a.Points, a.audit_id, a.question_type,
a.weighted, a.na, a.pf, a.nw, a.wh_survey_id, a.wh_question_id,
a.audit_date, a.survey_name, a.user_name, a.user_email, a.location_id,
c.question_section_name as question_section_name_2, c.rating,
c.survey_threshold_success, c.survey_threshold_failure, c.survey_threshold,
c.maximum_points, c.scored_points, c.percent_scored_of_max,
c.percent_total_score
FROM vw_audit_detail_report a
INNER JOIN vw_audit_detail_report_summary c ON a.audit_id = c.audit_id AND
a.question_section_name = c.question_section_name
WHERE (a.location_id = 4919
AND (a.audit_date BETWEEN '2003-07-11' AND '2003-07-11'))

What indexes do you have on the underlying tables?

|||If you are doing this in a stored procedure you might also want to consider dumping the first to a #temp table, and then inserting the 2nd into the same table, with a 3rd query to pull the data out of the #temp table. Once the procedure finishes the #temp table will disappear. Also as the other person suggested, look at your indexes. Put this query in query analyzer and get the execution plan to see where some of the backup is coming from. I see many of your joins are to views as well. Unless they are sorted and indexed as well, that can cause some issue. for something this complicated I usually try to get everything I can directly from the tables involved. views on views on views is never a good thing, and extremely difficult to diagnose!

my 2 cents

|||

A couple of comments on the first query: (NOTE: this may not make any difference...)

The code marked with Blue seems overly restrictive. It will only return rows that occurred at EXACTLY midnight on July 11, 2003. Are you sure you didn't mean to include all rows throughout the date of July 11th?

The code marked with Yellow seems unneeded and 'may' contribute to a slow response. You are doing a JOIN with a subset of data that is already constrained by the same criteria. Does this add anything? Is it possible that the Audit_ID values in either table have would have changed for that date range?

The code marked with Orange seems unneeded. The JOIN conditions with vw_audit_detail_report_summary (derived table 'b') seem to have previous precluded question_section_name being in both tables.

Code Snippet

FROM vw_audit_detail_report d
JOIN (SELECT TOP 1 *
FROM vw_audit_detail_report_summary e
WHERE ( e.location_id = 4919
AND e.audit_date BETWEEN '2003-07-11' AND '2003-07-11'
)
) b
ON ( d.audit_id = b.audit_id
AND d.question_section_name <> b.question_section_name
)
WHERE ( ( d.location_id = 4919
AND d.audit_date BETWEEN '2003-07-11' AND '2003-07-11'
)
AND d.question_section_name NOT IN (SELECT question_section_name
FROM vw_audit_detail_report_summary f
WHERE ( f.location_id = 4919
AND f.audit_date BETWEEN '2003-07-11' AND '2003-07-11'
)
)
)

Unless I'm really misreading this (and on Monday, anything is possible), it seems like the query could be revised as:

Code Snippet

FROM vw_audit_detail_report d
JOIN (SELECT TOP 1 *
FROM vw_audit_detail_report_summary e
WHERE ( e.location_id = 4919
AND e.audit_date BETWEEN '2003-07-11' AND '2003-07-11'
)
) b
ON ( d.audit_id = b.audit_id
AND d.question_section_name <> b.question_section_name
)

And then you do a UNION with the second query, so it seems that the

AND d.question_section_name <> b.question_section_name

and the entire second query, could also be eliminated ...

|||thanks so much.... that solved my problem...

how to make query run faster?

hello i have two queries that i need to combine.... the first queryexecutes within one second and the second query executes within 7 seconds.... I used UNION to join this two query.. but my problem is that when using UNION my query now executes within 3:00 minutes?... how can i make my query run faster?... thanks so much....

here's my query:

SELECT d.question_section_name, d.question_sort_order, d.question_number,
d.question_text, d.Range, d.answer, d.Points, d.audit_id, d.question_type,
d.weighted, d.na, d.pf, d.nw, d.wh_survey_id, d.wh_question_id,
d.audit_date, d.survey_name, d.user_name, d.user_email, d.location_id,
b.question_section_name as question_section_name_2, b.rating,
b.survey_threshold_success, b.survey_threshold_failure, b.survey_threshold,
b.maximum_points, b.scored_points, b.percent_scored_of_max,
b.percent_total_score
FROM vw_audit_detail_report d JOIN (SELECT TOP 1 * FROM
vw_audit_detail_report_summary e
WHERE (e.location_id = 4919
AND (e.audit_date BETWEEN '2003-07-11' AND '2003-07-11'))) b
ON d.audit_id = b.audit_id and (d.question_section_name <>
b.question_section_name)
WHERE (d.location_id = 4919
AND (d.audit_date BETWEEN '2003-07-11' AND '2003-07-11'))
And d.question_section_name NOT IN (SELECT question_section_name
FROM vw_audit_detail_report_summary f where
(f.location_id = 4919
AND (f.audit_date BETWEEN '2003-07-11' AND '2003-07-11')))

UNION

SELECT a.question_section_name, a.question_sort_order, a.question_number,
a.question_text, a.Range, a.answer, a.Points, a.audit_id, a.question_type,
a.weighted, a.na, a.pf, a.nw, a.wh_survey_id, a.wh_question_id,
a.audit_date, a.survey_name, a.user_name, a.user_email, a.location_id,
c.question_section_name as question_section_name_2, c.rating,
c.survey_threshold_success, c.survey_threshold_failure, c.survey_threshold,
c.maximum_points, c.scored_points, c.percent_scored_of_max,
c.percent_total_score
FROM vw_audit_detail_report a
INNER JOIN vw_audit_detail_report_summary c ON a.audit_id = c.audit_id AND
a.question_section_name = c.question_section_name
WHERE (a.location_id = 4919
AND (a.audit_date BETWEEN '2003-07-11' AND '2003-07-11'))

What indexes do you have on the underlying tables?

|||If you are doing this in a stored procedure you might also want to consider dumping the first to a #temp table, and then inserting the 2nd into the same table, with a 3rd query to pull the data out of the #temp table. Once the procedure finishes the #temp table will disappear. Also as the other person suggested, look at your indexes. Put this query in query analyzer and get the execution plan to see where some of the backup is coming from. I see many of your joins are to views as well. Unless they are sorted and indexed as well, that can cause some issue. for something this complicated I usually try to get everything I can directly from the tables involved. views on views on views is never a good thing, and extremely difficult to diagnose!

my 2 cents

|||

A couple of comments on the first query: (NOTE: this may not make any difference...)

The code marked with Blue seems overly restrictive. It will only return rows that occurred at EXACTLY midnight on July 11, 2003. Are you sure you didn't mean to include all rows throughout the date of July 11th?

The code marked with Yellow seems unneeded and 'may' contribute to a slow response. You are doing a JOIN with a subset of data that is already constrained by the same criteria. Does this add anything? Is it possible that the Audit_ID values in either table have would have changed for that date range?

The code marked with Orange seems unneeded. The JOIN conditions with vw_audit_detail_report_summary (derived table 'b') seem to have previous precluded question_section_name being in both tables.

Code Snippet

FROM vw_audit_detail_report d
JOIN (SELECT TOP 1 *
FROM vw_audit_detail_report_summary e
WHERE ( e.location_id = 4919
AND e.audit_date BETWEEN '2003-07-11' AND '2003-07-11'
)
) b
ON ( d.audit_id = b.audit_id
AND d.question_section_name <> b.question_section_name
)
WHERE ( ( d.location_id = 4919
AND d.audit_date BETWEEN '2003-07-11' AND '2003-07-11'
)
AND d.question_section_name NOT IN (SELECT question_section_name
FROM vw_audit_detail_report_summary f
WHERE ( f.location_id = 4919
AND f.audit_date BETWEEN '2003-07-11' AND '2003-07-11'
)
)
)

Unless I'm really misreading this (and on Monday, anything is possible), it seems like the query could be revised as:

Code Snippet

FROM vw_audit_detail_report d
JOIN (SELECT TOP 1 *
FROM vw_audit_detail_report_summary e
WHERE ( e.location_id = 4919
AND e.audit_date BETWEEN '2003-07-11' AND '2003-07-11'
)
) b
ON ( d.audit_id = b.audit_id
AND d.question_section_name <> b.question_section_name
)

And then you do a UNION with the second query, so it seems that the

AND d.question_section_name <> b.question_section_name

and the entire second query, could also be eliminated ...

|||thanks so much.... that solved my problem...

How to make query result fast

I have around 5 items in my database. It takes me about 1-
2 minutes to show up all the result. How can I make my
query result faster? I try to select everything on my
product table.
Hi,
Did you mean 5 tables and on running a query against this 5 tables takes 2
minutes.
The speed depends up on the availability of data in each of your tables, If
you have more records in table then create indexes based on your queries
Where condition.
This will defenetely speed up the retrieval time.
Note:
Use the Query -- Execution plan option inside Query analyzer to tune the
query.
Thanks
Hari
MCDBA
"ping" <anonymous@.discussions.microsoft.com> wrote in message
news:667501c42e80$9e398d80$a001280a@.phx.gbl...
> I have around 5 items in my database. It takes me about 1-
> 2 minutes to show up all the result. How can I make my
> query result faster? I try to select everything on my
> product table.
|||I would say that this is a little open ended question. Speed of the query
is not related to just one aspect.
Lets take scenarios here. First, lets assume that you have one table with
only 5 rows in it. But each row has 150 columns (say for the sake of
discussion). So if you run a query which is like SELECT * FROM TABLEA, this
is gonna take a lot of time, as this will have to result all the columns
from the table. Therefore, a better way would be to select only those
columns that you actually need in your result set. Say if you need only
5-10 columns, then there is no need to return 150 columns.
Second, lets assume that you 2000 rows but each row has only 3 columns.
Even if you return all the columns, your query will still be fast. So you
dont have to worry.
Another way to fasten your results is to have indexes created on each
table. This will also fasten up your results. You can even analyse your
query performance by making use of the Query Execution plan.
So at the end of the day, it depends. Your query is slightly open ended so
I would recommend that you re-visit it and evaluate it further to see its
performance cost.
Sanchan [MSFT]
sanchans@.online.microsoft.com
This posting is provided "AS IS" with no warranties, and confers no rights.

How to make multiple-valued in one parameter in Query Builder?

Dear all,

I have a problem over here, hope to get some good advices from you guys.

I use Query Builder in VS2005 where you could see some tables on top, queries on the middle and resultset on the bottom windows.

I have a statement like below:

SELECT...WHERE Booking.BookingType IN (@.Type1, @.Type2, @.Type3, @.Type4, @.Type5)...

However, I found out that there are too many parameters, I wish to have only ONE parameters in the list of IN() operator. Is there any way to do it? Thanks!

I would suggest giving a look to Jens Suessmeyer's SPLIT function; the definition of this function can be found here:

http://forums.microsoft.com/TechNet/ShowPost.aspx?PostID=419984&SiteID=17

You ought to be able to code up your lists as comma separated strings and create a query that

looks something like this:

Code Snippet

SELECT...

from Booking

join split (@.yourParm, ',')

on Booking.BookingType = splitValue

Another alternative is to pass your argument as XML and use SQL Servers XML ability to interpret the XML argument.

Another question: Are you trying to make a parameter similar to a multi-value parameter that is available in Reporting Services? If the answer to that question is "YES" then you need to understand that feature is only available in Reporting Services and is not in general available to Transact SQL.

|||

If you are trying to reduce the number of parameters, just remove them from inside the paretheses.

In fact, if you are ONLY testing for a single parameter, use equals instead of IN (...) -it may be a bit more efficient.

|||

Actually, what I wish to have is putting a list of values in the @.Param programmatically in my code, thus, I wish to have one single @.Param instead of multiple @.Param(s) in SQL. I used Query Builder GUI to build my SQL query in VS2005, however, I couldn't find way to just put one @.Param and set multiple values in it.

For e.g.

SELECT * FROM Booking WHERE Type IN ( @.Param );

C# Code:

....DataTable1.GetData("Lab, Meeting, Course");

Is this possible to do this?

Thanks!

|||

There is no direct way to do something like

Code Snippet

SELECT * FROM Booking WHERE Type IN ( @.Param );

in which @.param contains data such as 'Lab, Meeting, Course'. Again, I would suggest using the SPLIT function to accomplish your objective.

How to make Fields dynamic in a table column?

I am developing a single dynamic report in report designer and I have
a small problem. My report has several parameters and I use MDX to
query from a Cube(Analysis Services). Everything works fine until I
want a field in the report to change dynamically when a user changes a
parameter.
Example:
I have one parameter, dimension, which is a drop down list. If the
user chooses 'region' as the dimension, I want the Field!Region.Value
to be my field in the table column. If he chooses 'store' I want
Field!Store.Value to show instead of Region or any other dimension in
the column. Is there any way to make a report dynamic like this in
report designer?
At this moment I have to define fields with the same adress as the
database location: 'Store' has field expression like this -
[Customer].[Store].[MEMBER_CAPTION] and if I use this expression in
the dimension field when the user chooses 'Store' from the drop down
list, I`ll get all the results displayed in the table. This is what I
want to make more dynamic.
My simplyfied MDX query:
SELECT {[Measure].[Units Ordered]} on Columns,
{[Customer].[" + Parameters!Dimension.Value + "].Members} on Rows
from Orders
Does anyone have a solution to this problem?You can use the collection-based syntax for referencing field names in the
report layout. E.g.
=Fields(Parameters!DimensionSelection.Value).Value
Based on the actual parameter value, the field name will be determined at
report processing time.
-- Robert
This posting is provided "AS IS" with no warranties, and confers no rights.
"Oddvar Brennhovd" <oddvar.brennhovd@.isolutions.no> wrote in message
news:83475cf2.0502230717.48b871f6@.posting.google.com...
>I am developing a single dynamic report in report designer and I have
> a small problem. My report has several parameters and I use MDX to
> query from a Cube(Analysis Services). Everything works fine until I
> want a field in the report to change dynamically when a user changes a
> parameter.
> Example:
> I have one parameter, dimension, which is a drop down list. If the
> user chooses 'region' as the dimension, I want the Field!Region.Value
> to be my field in the table column. If he chooses 'store' I want
> Field!Store.Value to show instead of Region or any other dimension in
> the column. Is there any way to make a report dynamic like this in
> report designer?
> At this moment I have to define fields with the same adress as the
> database location: 'Store' has field expression like this -
> [Customer].[Store].[MEMBER_CAPTION] and if I use this expression in
> the dimension field when the user chooses 'Store' from the drop down
> list, I`ll get all the results displayed in the table. This is what I
> want to make more dynamic.
> My simplyfied MDX query:
> SELECT {[Measure].[Units Ordered]} on Columns,
> {[Customer].[" + Parameters!Dimension.Value + "].Members} on Rows
> from Orders
> Does anyone have a solution to this problem?|||Thank you. Now the problem is solved!

How to make distributed query by SQL Express

I am going to make a distributed query, but in the query design view I cannot add the tables from another server, why?

hi,

AFAIK, the designers only show objects local to the connected server (and database)... but you can easely insert the required code pointing to remote linked servers...

regards

|||Thanks, i will try.

How to make a random order query ?

If we use: select * from .... , normally, it will return an ordered result ( may be order by ID ), but how can we make an random order select statement. It mean every time we run the query, the result will be different from the other ?Hi,

FYI:http://www.datawebcontrols.com/faqs/Data/ReturningDataInRandomOrder.shtml

Regards,

how to maintain database concurrence?

My application uses sql for performing operation. something like conncetion.execute(query). so there is only conncetion object no recordset object or something like that .

i want to run multiple instence of my application so i want to maintain integrety of data. and i am looking for solution through sql for locking mechanism. so concurrent data access dont currept data.

I hope you have uderstand my requirement.Avoid locking whenever possible! Use optimistic concurrency (http://msdn.microsoft.com/library/default.asp?url=/library/en-us/odbc/htm/odbcoptimistic_concurrency.asp) instead of relying on locking.

-PatP

how to made adhoc query cached?

Hi,
I have a problem with performance of ADHOC query like "exec sp_Name
par1=@.par1 ... parN=@.parN". I know that there are prox. 50 different kinds of
the query ( with different parameter value ). I found in syscacheobjects only
20% of cases. And most heavy query is not cached at all. The execution time
of this particular query is 10 times more than if it was cached. Do somebody
know how to made sqlserver to cache particular adhoc query?
Thanks.
Leo
Did you see an execution plan of the query? Did the optimizer
use any indexes defined on the tables?
How big are your tables?
How many rows do you want to retrieve?
"Leo" <Leo@.discussions.microsoft.com> wrote in message
news:80BD14F0-FF7A-43C7-BBE6-F7C241889597@.microsoft.com...
> Hi,
> I have a problem with performance of ADHOC query like "exec sp_Name
> par1=@.par1 ... parN=@.parN". I know that there are prox. 50 different kinds
of
> the query ( with different parameter value ). I found in syscacheobjects
only
> 20% of cases. And most heavy query is not cached at all. The execution
time
> of this particular query is 10 times more than if it was cached. Do
somebody
> know how to made sqlserver to cache particular adhoc query?
> Thanks.
>
|||Of cource, I saw the plan under query analizer, and this plan quite satisfy me.
The problem is that the application sometimes use another plan. The query in
plan I see in QA uses tables indexes. The size is not very big - less than
100K records,
but query uses aggregations.
"Uri Dimant" wrote:

> Leo
> Did you see an execution plan of the query? Did the optimizer
> use any indexes defined on the tables?
> How big are your tables?
> How many rows do you want to retrieve?
>
>
>
> "Leo" <Leo@.discussions.microsoft.com> wrote in message
> news:80BD14F0-FF7A-43C7-BBE6-F7C241889597@.microsoft.com...
> of
> only
> time
> somebody
>
>
|||Leo
So , does it meant that query optimizer was available to use indexes?
Can you post your query and how you call it ?
"Leo" <Leo@.discussions.microsoft.com> wrote in message
news:B453A794-56BE-49AC-82E7-C117DDABC73E@.microsoft.com...
> Of cource, I saw the plan under query analizer, and this plan quite
satisfy me.
> The problem is that the application sometimes use another plan. The query
in[vbcol=seagreen]
> plan I see in QA uses tables indexes. The size is not very big - less than
> 100K records,
> but query uses aggregations.
>
> "Uri Dimant" wrote:
kinds[vbcol=seagreen]
syscacheobjects[vbcol=seagreen]
|||It is nice of you , Uri to prompt me to find out the problems from source.
But I afraid it is impossible - the size of procedure is 17 kB and it is
really complicated
My question is how make sqlserver put the plan of this procedure to cache.
When I call dbcc freeproccache then call my procedure , it is goes to cache,
but after server restart and cache clearing it is disappiared. I need some
predictable behavior and want it always be in cache.
Thank you once more for assistance.
Leo
"Uri Dimant" wrote:

> Leo
> So , does it meant that query optimizer was available to use indexes?
> Can you post your query and how you call it ?
>
> "Leo" <Leo@.discussions.microsoft.com> wrote in message
> news:B453A794-56BE-49AC-82E7-C117DDABC73E@.microsoft.com...
> satisfy me.
> in
> kinds
> syscacheobjects
>
>
|||You can create an autostart procedure from which you execute this stored procedure. See
sp_procoption.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"Leo" <Leo@.discussions.microsoft.com> wrote in message
news:63F79347-2F0F-4F76-B318-77C3AEC167B2@.microsoft.com...[vbcol=seagreen]
> It is nice of you , Uri to prompt me to find out the problems from source.
> But I afraid it is impossible - the size of procedure is 17 kB and it is
> really complicated
> My question is how make sqlserver put the plan of this procedure to cache.
> When I call dbcc freeproccache then call my procedure , it is goes to cache,
> but after server restart and cache clearing it is disappiared. I need some
> predictable behavior and want it always be in cache.
> Thank you once more for assistance.
> Leo
> "Uri Dimant" wrote:
|||Thanks
"Tibor Karaszi" wrote:

> You can create an autostart procedure from which you execute this stored procedure. See
> sp_procoption.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> http://www.sqlug.se/
>
> "Leo" <Leo@.discussions.microsoft.com> wrote in message
> news:63F79347-2F0F-4F76-B318-77C3AEC167B2@.microsoft.com...
>
>

how to made adhoc query cached?

Hi,
I have a problem with performance of ADHOC query like "exec sp_Name
par1=@.par1 ... parN=@.parN". I know that there are prox. 50 different kinds o
f
the query ( with different parameter value ). I found in syscacheobjects onl
y
20% of cases. And most heavy query is not cached at all. The execution time
of this particular query is 10 times more than if it was cached. Do somebody
know how to made sqlserver to cache particular adhoc query?
Thanks.Leo
Did you see an execution plan of the query? Did the optimizer
use any indexes defined on the tables?
How big are your tables?
How many rows do you want to retrieve?
"Leo" <Leo@.discussions.microsoft.com> wrote in message
news:80BD14F0-FF7A-43C7-BBE6-F7C241889597@.microsoft.com...
> Hi,
> I have a problem with performance of ADHOC query like "exec sp_Name
> par1=@.par1 ... parN=@.parN". I know that there are prox. 50 different kinds
of
> the query ( with different parameter value ). I found in syscacheobjects
only
> 20% of cases. And most heavy query is not cached at all. The execution
time
> of this particular query is 10 times more than if it was cached. Do
somebody
> know how to made sqlserver to cache particular adhoc query?
> Thanks.
>|||Of cource, I saw the plan under query analizer, and this plan quite satisfy
me.
The problem is that the application sometimes use another plan. The query in
plan I see in QA uses tables indexes. The size is not very big - less than
100K records,
but query uses aggregations.
"Uri Dimant" wrote:

> Leo
> Did you see an execution plan of the query? Did the optimizer
> use any indexes defined on the tables?
> How big are your tables?
> How many rows do you want to retrieve?
>
>
>
> "Leo" <Leo@.discussions.microsoft.com> wrote in message
> news:80BD14F0-FF7A-43C7-BBE6-F7C241889597@.microsoft.com...
> of
> only
> time
> somebody
>
>|||Leo
So , does it meant that query optimizer was available to use indexes?
Can you post your query and how you call it ?
"Leo" <Leo@.discussions.microsoft.com> wrote in message
news:B453A794-56BE-49AC-82E7-C117DDABC73E@.microsoft.com...
> Of cource, I saw the plan under query analizer, and this plan quite
satisfy me.
> The problem is that the application sometimes use another plan. The query
in[vbcol=seagreen]
> plan I see in QA uses tables indexes. The size is not very big - less than
> 100K records,
> but query uses aggregations.
>
> "Uri Dimant" wrote:
>
kinds[vbcol=seagreen]
syscacheobjects[vbcol=seagreen]|||It is nice of you , Uri to prompt me to find out the problems from source.
But I afraid it is impossible - the size of procedure is 17 kB and it is
really complicated
My question is how make sqlserver put the plan of this procedure to cache.
When I call dbcc freeproccache then call my procedure , it is goes to cache,
but after server restart and cache clearing it is disappiared. I need some
predictable behavior and want it always be in cache.
Thank you once more for assistance.
Leo
"Uri Dimant" wrote:

> Leo
> So , does it meant that query optimizer was available to use indexes?
> Can you post your query and how you call it ?
>
> "Leo" <Leo@.discussions.microsoft.com> wrote in message
> news:B453A794-56BE-49AC-82E7-C117DDABC73E@.microsoft.com...
> satisfy me.
> in
> kinds
> syscacheobjects
>
>|||You can create an autostart procedure from which you execute this stored pro
cedure. See
sp_procoption.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"Leo" <Leo@.discussions.microsoft.com> wrote in message
news:63F79347-2F0F-4F76-B318-77C3AEC167B2@.microsoft.com...[vbcol=seagreen]
> It is nice of you , Uri to prompt me to find out the problems from source.
> But I afraid it is impossible - the size of procedure is 17 kB and it is
> really complicated
> My question is how make sqlserver put the plan of this procedure to cache.
> When I call dbcc freeproccache then call my procedure , it is goes to cach
e,
> but after server restart and cache clearing it is disappiared. I need some
> predictable behavior and want it always be in cache.
> Thank you once more for assistance.
> Leo
> "Uri Dimant" wrote:
>|||Thanks
"Tibor Karaszi" wrote:

> You can create an autostart procedure from which you execute this stored p
rocedure. See
> sp_procoption.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> http://www.sqlug.se/
>
> "Leo" <Leo@.discussions.microsoft.com> wrote in message
> news:63F79347-2F0F-4F76-B318-77C3AEC167B2@.microsoft.com...
>
>sql

how to made adhoc query cached?

Hi,
I have a problem with performance of ADHOC query like "exec sp_Name
par1=@.par1 ... parN=@.parN". I know that there are prox. 50 different kinds of
the query ( with different parameter value ). I found in syscacheobjects only
20% of cases. And most heavy query is not cached at all. The execution time
of this particular query is 10 times more than if it was cached. Do somebody
know how to made sqlserver to cache particular adhoc query?
Thanks.Leo
Did you see an execution plan of the query? Did the optimizer
use any indexes defined on the tables?
How big are your tables?
How many rows do you want to retrieve?
"Leo" <Leo@.discussions.microsoft.com> wrote in message
news:80BD14F0-FF7A-43C7-BBE6-F7C241889597@.microsoft.com...
> Hi,
> I have a problem with performance of ADHOC query like "exec sp_Name
> par1=@.par1 ... parN=@.parN". I know that there are prox. 50 different kinds
of
> the query ( with different parameter value ). I found in syscacheobjects
only
> 20% of cases. And most heavy query is not cached at all. The execution
time
> of this particular query is 10 times more than if it was cached. Do
somebody
> know how to made sqlserver to cache particular adhoc query?
> Thanks.
>|||Of cource, I saw the plan under query analizer, and this plan quite satisfy me.
The problem is that the application sometimes use another plan. The query in
plan I see in QA uses tables indexes. The size is not very big - less than
100K records,
but query uses aggregations.
"Uri Dimant" wrote:
> Leo
> Did you see an execution plan of the query? Did the optimizer
> use any indexes defined on the tables?
> How big are your tables?
> How many rows do you want to retrieve?
>
>
>
> "Leo" <Leo@.discussions.microsoft.com> wrote in message
> news:80BD14F0-FF7A-43C7-BBE6-F7C241889597@.microsoft.com...
> > Hi,
> > I have a problem with performance of ADHOC query like "exec sp_Name
> > par1=@.par1 ... parN=@.parN". I know that there are prox. 50 different kinds
> of
> > the query ( with different parameter value ). I found in syscacheobjects
> only
> > 20% of cases. And most heavy query is not cached at all. The execution
> time
> > of this particular query is 10 times more than if it was cached. Do
> somebody
> > know how to made sqlserver to cache particular adhoc query?
> > Thanks.
> >
>
>|||Leo
So , does it meant that query optimizer was available to use indexes?
Can you post your query and how you call it ?
"Leo" <Leo@.discussions.microsoft.com> wrote in message
news:B453A794-56BE-49AC-82E7-C117DDABC73E@.microsoft.com...
> Of cource, I saw the plan under query analizer, and this plan quite
satisfy me.
> The problem is that the application sometimes use another plan. The query
in
> plan I see in QA uses tables indexes. The size is not very big - less than
> 100K records,
> but query uses aggregations.
>
> "Uri Dimant" wrote:
> > Leo
> > Did you see an execution plan of the query? Did the optimizer
> > use any indexes defined on the tables?
> > How big are your tables?
> > How many rows do you want to retrieve?
> >
> >
> >
> >
> >
> >
> > "Leo" <Leo@.discussions.microsoft.com> wrote in message
> > news:80BD14F0-FF7A-43C7-BBE6-F7C241889597@.microsoft.com...
> > > Hi,
> > > I have a problem with performance of ADHOC query like "exec sp_Name
> > > par1=@.par1 ... parN=@.parN". I know that there are prox. 50 different
kinds
> > of
> > > the query ( with different parameter value ). I found in
syscacheobjects
> > only
> > > 20% of cases. And most heavy query is not cached at all. The execution
> > time
> > > of this particular query is 10 times more than if it was cached. Do
> > somebody
> > > know how to made sqlserver to cache particular adhoc query?
> > > Thanks.
> > >
> >
> >
> >|||It is nice of you , Uri to prompt me to find out the problems from source.
But I afraid it is impossible - the size of procedure is 17 kB and it is
really complicated :)
My question is how make sqlserver put the plan of this procedure to cache.
When I call dbcc freeproccache then call my procedure , it is goes to cache,
but after server restart and cache clearing it is disappiared. I need some
predictable behavior and want it always be in cache.
Thank you once more for assistance.
Leo
"Uri Dimant" wrote:
> Leo
> So , does it meant that query optimizer was available to use indexes?
> Can you post your query and how you call it ?
>
> "Leo" <Leo@.discussions.microsoft.com> wrote in message
> news:B453A794-56BE-49AC-82E7-C117DDABC73E@.microsoft.com...
> > Of cource, I saw the plan under query analizer, and this plan quite
> satisfy me.
> > The problem is that the application sometimes use another plan. The query
> in
> > plan I see in QA uses tables indexes. The size is not very big - less than
> > 100K records,
> > but query uses aggregations.
> >
> >
> > "Uri Dimant" wrote:
> >
> > > Leo
> > > Did you see an execution plan of the query? Did the optimizer
> > > use any indexes defined on the tables?
> > > How big are your tables?
> > > How many rows do you want to retrieve?
> > >
> > >
> > >
> > >
> > >
> > >
> > > "Leo" <Leo@.discussions.microsoft.com> wrote in message
> > > news:80BD14F0-FF7A-43C7-BBE6-F7C241889597@.microsoft.com...
> > > > Hi,
> > > > I have a problem with performance of ADHOC query like "exec sp_Name
> > > > par1=@.par1 ... parN=@.parN". I know that there are prox. 50 different
> kinds
> > > of
> > > > the query ( with different parameter value ). I found in
> syscacheobjects
> > > only
> > > > 20% of cases. And most heavy query is not cached at all. The execution
> > > time
> > > > of this particular query is 10 times more than if it was cached. Do
> > > somebody
> > > > know how to made sqlserver to cache particular adhoc query?
> > > > Thanks.
> > > >
> > >
> > >
> > >
>
>|||You can create an autostart procedure from which you execute this stored procedure. See
sp_procoption.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"Leo" <Leo@.discussions.microsoft.com> wrote in message
news:63F79347-2F0F-4F76-B318-77C3AEC167B2@.microsoft.com...
> It is nice of you , Uri to prompt me to find out the problems from source.
> But I afraid it is impossible - the size of procedure is 17 kB and it is
> really complicated :)
> My question is how make sqlserver put the plan of this procedure to cache.
> When I call dbcc freeproccache then call my procedure , it is goes to cache,
> but after server restart and cache clearing it is disappiared. I need some
> predictable behavior and want it always be in cache.
> Thank you once more for assistance.
> Leo
> "Uri Dimant" wrote:
>> Leo
>> So , does it meant that query optimizer was available to use indexes?
>> Can you post your query and how you call it ?
>>
>> "Leo" <Leo@.discussions.microsoft.com> wrote in message
>> news:B453A794-56BE-49AC-82E7-C117DDABC73E@.microsoft.com...
>> > Of cource, I saw the plan under query analizer, and this plan quite
>> satisfy me.
>> > The problem is that the application sometimes use another plan. The query
>> in
>> > plan I see in QA uses tables indexes. The size is not very big - less than
>> > 100K records,
>> > but query uses aggregations.
>> >
>> >
>> > "Uri Dimant" wrote:
>> >
>> > > Leo
>> > > Did you see an execution plan of the query? Did the optimizer
>> > > use any indexes defined on the tables?
>> > > How big are your tables?
>> > > How many rows do you want to retrieve?
>> > >
>> > >
>> > >
>> > >
>> > >
>> > >
>> > > "Leo" <Leo@.discussions.microsoft.com> wrote in message
>> > > news:80BD14F0-FF7A-43C7-BBE6-F7C241889597@.microsoft.com...
>> > > > Hi,
>> > > > I have a problem with performance of ADHOC query like "exec sp_Name
>> > > > par1=@.par1 ... parN=@.parN". I know that there are prox. 50 different
>> kinds
>> > > of
>> > > > the query ( with different parameter value ). I found in
>> syscacheobjects
>> > > only
>> > > > 20% of cases. And most heavy query is not cached at all. The execution
>> > > time
>> > > > of this particular query is 10 times more than if it was cached. Do
>> > > somebody
>> > > > know how to made sqlserver to cache particular adhoc query?
>> > > > Thanks.
>> > > >
>> > >
>> > >
>> > >
>>|||Thanks
"Tibor Karaszi" wrote:
> You can create an autostart procedure from which you execute this stored procedure. See
> sp_procoption.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> http://www.sqlug.se/
>
> "Leo" <Leo@.discussions.microsoft.com> wrote in message
> news:63F79347-2F0F-4F76-B318-77C3AEC167B2@.microsoft.com...
> > It is nice of you , Uri to prompt me to find out the problems from source.
> > But I afraid it is impossible - the size of procedure is 17 kB and it is
> > really complicated :)
> > My question is how make sqlserver put the plan of this procedure to cache.
> > When I call dbcc freeproccache then call my procedure , it is goes to cache,
> > but after server restart and cache clearing it is disappiared. I need some
> > predictable behavior and want it always be in cache.
> >
> > Thank you once more for assistance.
> >
> > Leo
> >
> > "Uri Dimant" wrote:
> >
> >> Leo
> >> So , does it meant that query optimizer was available to use indexes?
> >> Can you post your query and how you call it ?
> >>
> >>
> >>
> >> "Leo" <Leo@.discussions.microsoft.com> wrote in message
> >> news:B453A794-56BE-49AC-82E7-C117DDABC73E@.microsoft.com...
> >> > Of cource, I saw the plan under query analizer, and this plan quite
> >> satisfy me.
> >> > The problem is that the application sometimes use another plan. The query
> >> in
> >> > plan I see in QA uses tables indexes. The size is not very big - less than
> >> > 100K records,
> >> > but query uses aggregations.
> >> >
> >> >
> >> > "Uri Dimant" wrote:
> >> >
> >> > > Leo
> >> > > Did you see an execution plan of the query? Did the optimizer
> >> > > use any indexes defined on the tables?
> >> > > How big are your tables?
> >> > > How many rows do you want to retrieve?
> >> > >
> >> > >
> >> > >
> >> > >
> >> > >
> >> > >
> >> > > "Leo" <Leo@.discussions.microsoft.com> wrote in message
> >> > > news:80BD14F0-FF7A-43C7-BBE6-F7C241889597@.microsoft.com...
> >> > > > Hi,
> >> > > > I have a problem with performance of ADHOC query like "exec sp_Name
> >> > > > par1=@.par1 ... parN=@.parN". I know that there are prox. 50 different
> >> kinds
> >> > > of
> >> > > > the query ( with different parameter value ). I found in
> >> syscacheobjects
> >> > > only
> >> > > > 20% of cases. And most heavy query is not cached at all. The execution
> >> > > time
> >> > > > of this particular query is 10 times more than if it was cached. Do
> >> > > somebody
> >> > > > know how to made sqlserver to cache particular adhoc query?
> >> > > > Thanks.
> >> > > >
> >> > >
> >> > >
> >> > >
> >>
> >>
> >>
>
>