Databases
StackOverflow2013
Postsdbo
POIs there a way to optimize the query given below
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POIs there a way to optimize the query given below
    primarykey
    data
    text
    <p>I have the following Query and i need the query to fetch data from SomeTable based on the filter criteria present in the Someothertable. If there is nothing present in SomeOtherTable Query should return me all the data present in SomeTable</p> <p>SQL SERVER 2005</p> <p>SomeOtherTable does not have any indexes or any constraint all fields are char(50)</p> <p>The Following Query work fine for my requirements but it causes performance problems when i have lots of parameters.</p> <p>Due to some requirement of Client, We have to keep all the Where clause data in SomeOtherTable. depending on subid data will be joined with one of the columns in SomeTable.</p> <p>For example the Query can can be </p> <pre><code>SELECT * FROM SomeTable WHERE 1=1 AND ( SomeTable.ID in (SELECT DISTINCT ID FROM SomeOtherTable WHERE Name = 'ABC' and subid = 'EF') OR 0=(SELECT Count(1) FROM SomeOtherTable WHERE spName = 'ABC' and subid = 'EF') ) AND ( SomeTable.date =(SELECT date FROM SomeOtherTable WHERE Name = 'ABC' and subid = 'Date') OR 0=(SELECT Count(1) FROM SomeOtherTable WHERE spName = 'ABC' and subid = 'Date') ) </code></pre> <p>EDIT----------------------------------------------</p> <p>I think i might have to explain my problem in detail:</p> <p>We have developed an ASP.net application that is used to invoke parametrize crystal reports, parameters to the crystal reports are not passed using the default crystal reports method.</p> <p>In ASP.net application we have created wizards which are used to pass the parameters to the Reports, These parameters are not directly consumed by the crystal report but are consumed by the Query embedded inside the crystal report or the Stored procedure used in the Crystal report.</p> <p>This is achieved using a table (SomeOtherTable) which holds parameter data as long as report is running after which the data is deleted, as such we can assume that SomeOtherTable has max 2 to 3 rows at any given point of time.</p> <p>So if we look at the above query initial part of the Query can be assumed as the Report Query and the where clause is used to get the user input from the SomeOtherTable table.</p> <p>So i don't think it will be useful to create indexes etc (May be i am wrong).</p>
    singulars
    1. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. PTQuestion
    1. USrsapru
    1. USrsapru
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    2. PO
      singulars
      1. PTAnswer
    3. PO
      singulars
      1. PTAnswer
    1. VO
      singulars
      1. POIs there a way to optimize the query given below
      1. This table or related slice is empty.
      1. VTDownMod
    2. VO
      singulars
      1. POIs there a way to optimize the query given below
      1. This table or related slice is empty.
      1. VTDownMod
    3. VO
      singulars
      1. POIs there a way to optimize the query given below
      1. This table or related slice is empty.
      1. VTDownMod
    1. COCan you post sample DDL of the SomeTable and SomeOtherTable tables (and indexes)? Without that, you will not get good answers on how to make this perform best.
      singulars
      1. POIs there a way to optimize the query given below
      1. USStingyJack
    2. COWhat version of SQL Server are you using? That will also influence the answers correctness.
      singulars
      1. POIs there a way to optimize the query given below
      1. USStingyJack
    3. COjust wondering, why was this downvoted?
      singulars
      1. POIs there a way to optimize the query given below
      1. USmelaos
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload