Databases
StackOverflow2013
Postsdbo
POAnother approach for long SQL Query
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POAnother approach for long SQL Query
    primarykey
    data
    text
    <p>I have a query whose purpose is to retrieve the records according to search parameters. Other parameters are fine but there's one field called "Member". When users put a part of name to that "Member" text box, it should return all the records in which that "Member" has been involved in different roles. I had quite a complex query but it was fine and only took a second before the where clause is put. My previous where clause was like</p> <pre><code>WHERE (( C.IDNumber LIKE '%11%' AND C.OriginalIDNumber LIKE '%11%' AND H.UserDisplayName LIKE '%david%' OR C.SubmittedBy LIKE '%david%' OR J.UserID IN( Select UserID from USER_PROFILE where UserDisplayName LIKE '%david%') OR L.UserID IN( Select UserID from USER_PROFILE where UserDisplayName LIKE '%david%') OR X.DepartmentHead IN (Select UserID from USER_PROFILE where UserDisplayName LIKE '%david%') OR E.DepartmentHead IN (Select UserID from USER_PROFILE where UserDisplayName LIKE '%david%') OR N.UserID IN (Select UserID from USER_PROFILE where UserDisplayName LIKE '%david%') OR O.UserID IN (Select UserID from USER_PROFILE where UserDisplayName LIKE '%david%') OR Y.OrganisationAdmin IN (Select UserID from USER_PROFILE where UserDisplayName LIKE '%david%') OR Y.FinalApprovalAuthority IN (Select UserID from USER_PROFILE where UserDisplayName LIKE '%david%') OR Y.OrganisationFinance IN (Select UserID from USER_PROFILE where UserDisplayName LIKE '%david%') OR Z.UserID IN (Select UserID from USER_PROFILE where UserDisplayName LIKE '%david%'))) AND (C.Status&lt;&gt;'Draft') </code></pre> <p>J,L,X,E and so on are different tables that I must join. I took more than a minute and I modifed it into</p> <pre><code>WHERE (( C.IDNumber LIKE '%11%' AND C.OriginalIDNumber LIKE '%11%' AND H.UserDisplayName LIKE '%david%' OR C.SubmittedBy LIKE '%david%' OR W.UserDisplayName LIKE '%david%' OR AA.UserDisplayName LIKE '%david%' OR BB.UserDisplayName LIKE '%david%' OR CC.UserDisplayName LIKE '%david%' OR DD.UserDisplayName LIKE '%david%' OR EE.UserDisplayName LIKE '%david%' OR FF.UserDisplayName LIKE '%david%' OR GG.UserDisplayName LIKE '%david%' OR HH.UserDisplayName LIKE '%david%' OR II.UserDisplayName LIKE '%david%')) AND (C.Status&lt;&gt;'Draft') </code></pre> <p>by putting left outer join for User_Profile table with J,L,X,E and so on. AA, BB, CC and so on are new User_Profile tables to be joined.</p> <p>But it is still taking 30 seconds which leads the "Timeout Expired" error when I run the application. Any other approach to reduce the time taking by query?</p> <p>I'm using MSSQL 2008.</p>
    singulars
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PTQuestion
    1. USdaydream
    1. USdaydream
    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. This table or related slice is empty.
    1. COPlease include the relevant database information (e.g. *which* RDBMS?) as well as any relevant *execution plans* and *indexes*. I prefer to use JOINS vs sub-queries here -- and while they both *could* be optimized to the same plan, this will vary based on RDMBs. Since `Select UserID from USER_PROFILE where UserDisplayName LIKE '%david%'` is *highly repeated* and thus should likely be eliminated to a single join (again, this *could* be optimized out by the query optimizer (depends on RDMBS and optimizations!), but there is no reason for such duplication in the query!).
      singulars
      1. POAnother approach for long SQL Query
      1. This table or related slice is empty.
    2. COAlso, the fact that all the tables have a `UserDisplayName` sounds like incorrect/insufficient normalization :)
      singulars
      1. POAnother approach for long SQL Query
      1. This table or related slice is empty.
    3. COYes, Since Select UserID from USER_PROFILE where UserDisplayName LIKE '%david%' is repeated, I changed it. It couldn't be solved by single join because once you join the User_Profile table with another table, it becomes limited. The only way I've found so far is to left outer join the User_Profile table with each & every table that need to be joined. UserDisplayName is for display purpose only and it's only from User_Profile table not from others.
      singulars
      1. POAnother approach for long SQL Query
      1. USdaydream
 

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