Databases
StackOverflow2013
Postsdbo
PO
 

Note that there are some explanatory texts on larger screens.

plurals
  1. PO
    primarykey
    data
    text
    <p><strong>EDIT2:</strong> Thinking about it, how many of those masks start <strong>and</strong> end with wildcards? You might gain some performance by first:</p> <ul> <li>Indexing <code>CUST_PO_NUMBER</code></li> <li>Creating a persisted computed column <code>CUST_PO_NUMBER_REV</code> that's the reverse of <code>CUST_PO_NUMBER</code></li> <li>Indexing the persisted column</li> <li>Putting statistics on these columns</li> </ul> <p>Then you might build three queries, and <code>UNION ALL</code> the results together:</p> <pre><code>SELECT ... FROM ... ON CUSTOM_PO_NUMBER LIKE CustomerMask WHERE /* First character of CustomerMask is not a wildcard but last one is */ UNION ALL SELECT ... FROM ... ON CUSTOM_PO_NUMBER_REV LIKE REVERSE(CustomerMask) WHERE /* Last character of CustomerMask is not a wildcard but first one is */ UNION ALL SELECT ... FROM ... ON CUSTOM_PO_NUMBER LIKE CustomerMask WHERE /* Everything else */ </code></pre> <p>That's just a quick example, you'll need to take some care that the <code>WHERE</code> clauses give you mutually exclusive results (or use <code>UNION</code>, but aim for mutually exclusive results first).</p> <p>If you can do that, you should have two queries using index seeks and one query using index scans.</p> <p><strong>EDIT:</strong> You can implement a sharding system to spread out the customers and customer masks tables across multiple servers and then have each server evaluate <code>1/n</code>% of the results. You don't need to partition the data -- simple replication of the entire contents of each table will do. Link the servers to your main server and you can do something to the effect of:</p> <pre><code>SELECT ... FROM OPENQUERY(LinkedServer1, 'SELECT ... LIKE ... WHERE ID BETWEEN 0 AND 99') UNION ALL SELECT ... FROM OPENQUERY(LinkedServer2, 'SELECT ... LIKE ... WHERE ID BETWEEN 100 AND 199') </code></pre> <p><sub>Note: the <code>OPENQUERY</code> may be extraneous, SQL Server might be smart enough to evaluate queries on remote servers and stream the results back. I know it doesn't do that for JET linked servers, but it might handle its own kind better.</sub></p> <p>That or through more hardware at the problem.</p> <p><strike>You can create an <a href="http://msdn.microsoft.com/en-us/library/ms191432.aspx" rel="nofollow">Indexed View</a> of your query to improve performance.</p> <p>From <a href="http://msdn.microsoft.com/en-us/library/ms187864.aspx" rel="nofollow"><em>Designing Indexed Views</em></a>:</p> <blockquote> <p>For a standard view, the overhead of dynamically building the result set for each query that references a view can be significant for views that involve complex processing of large numbers of rows, such as aggregating lots of data, or joining many rows. If such views are frequently referenced in queries, you can improve performance by creating a unique clustered index on the view. When a unique clustered index is created on a view, the result set is stored in the database just like a table with a clustered index is stored.</p> <p>Another benefit of creating an index on a view is that the optimizer starts using the view index in queries that do not directly name the view in the FROM clause. <strong>Existing queries can benefit from the improved efficiency of retrieving data from the indexed view without having to be recoded.</strong></p> </blockquote> <p>This should improve the performance of this particular query, but note that inserts, updates and deleted into the tables it uses may be slowed.</strike></p>
    singulars
    1. This table or related slice is empty.
    1. POTSQL join performance
      singulars
      1. PTQuestion
    1. PTAnswer
    1. USta.speot.is
    1. USta.speot.is
    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. This table or related slice is empty.
    1. This table or related slice is empty.
    1. COInset, update and delete are very important. This query is part of nightly process but insert and updates are constantly happening on this table. Cannot use Index view as this will have significant impact on our database.
      singulars
      1. PO
      1. USSQLGuru
    2. CO@SQLGuru Don't think there's all that much more you can do then, I've edited my answer with a suggestion but it's not all that practical.
      singulars
      1. PO
      1. USta.speot.is
    3. CO@SQLGuru Okay, one more edit.
      singulars
      1. PO
      1. USta.speot.is
 

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