Query optimization on a Table with 1 Million rows

Question

I'm doing a query over 1 million customers in SQL. Can this query be further optimized?

I'm trying to optimize the search criteria.

In the WHERE-clause:

SELECT * 
   FROM  [TB_AGENDA-VENDEDOR_VENDEDORES] AS Vend
   INNER JOIN [TB_AGENDA-VENDEDOR_CLIENTES] AS Cli
      ON Cli.EQUIPE_VENDA = Vend.EQUIPE_VENDA  
   WHERE Vend.EMAiL = 'email@email.com.uk' ORDER BY PROXIMA_LIGACAO ASC, CATEGORIA ASC

And in the ON-clause:

 SELECT * 
    FROM  [TB_AGENDA-VENDEDOR_VENDEDORES] AS Vend
    INNER JOIN [TB_AGENDA-VENDEDOR_CLIENTES] AS Cli
      ON Cli.EQUIPE_VENDA = Vend.EQUIPE_VENDA  AND Vend.EMAiL = 'email@email.com.uk'
ORDER BY PROXIMA_LIGACAO ASC, CATEGORIA ASC

or if you have other ideas or reference?

Use "Set statistics io on" to see I/O counters for the queries for comparison. For improvement ideas, add table & index structures to the post + query plan for the statement — James Z, Jul 15 '15 at 14:01
possible duplicate of [INNER JOIN ON vs WHERE clause](http://stackoverflow.com/questions/1018822/inner-join-on-vs-where-clause) — AHiggins, Jul 15 '15 at 14:04
almost doubled but my question is to optimize, anything forgive my ignorance — Richard Diogo, Jul 15 '15 at 14:06
@AHiggins the possible duplicate is a totally different topic. — Sean Lange, Jul 15 '15 at 14:07
The execution plans for both of those queries should be identical. The only to know for sure though is for you to run them and look at the actual execution plans (not the estimated one). — Sean Lange, Jul 15 '15 at 14:08

score 0 · Answer 1 · answered Jul 15 '15 at 14:08

For this query:

SELECT * 
FROM [TB_AGENDA-VENDEDOR_VENDEDORES] Vend INNER JOIN
     [TB_AGENDA-VENDEDOR_CLIENTES] AS Cli
     ON Cli.EQUIPE_VENDA = Vend.EQUIPE_VENDA  
WHERE Vend.EMAiL = 'email@email.com.uk'
ORDER BY PROXIMA_LIGACAO ASC, CATEGORIA ASC;

You probably want indexes on [TB_AGENDA-VENDEDOR_VENDEDORES](Email, EQUIPE_VENDA) and [TB_AGENDA-VENDEDOR_CLIENTES](EQUIPE_VENDA).

With more information about the nature of the tables and the columns that you actually need, it might be possible to make further suggestions.

I will test why it has to be compatible with the return of bcs sharepoint 2013 — Richard Diogo, Jul 15 '15 at 14:14

score 0 · Accepted Answer · answered Jul 15 '15 at 14:11

0

Well due to the fact that both should be interpreted the same, there isn't a real difference between them.

I would personally suggest to add an index to your column Vend.EMAiL. But only if your query runs more often than you add new rows to it. This really depends on the usage.

One example could be this index:

CREATE NONCLUSTERED INDEX NCI_TB_AGENDA_VENDEDOR_VENDEDORES_EMAIL 
    ON [TB_AGENDA-VENDEDOR_VENDEDORES] (EMAiL)

But as already said, it really depends on your usage and if you really want to retrieve all columns from both rows or only from one table or only some specific columns from each table.

answered Jul 15 '15 at 14:11

Ionic

3,884
1
12
33

I will test why it has to be compatible with the return of bcs sharepoint 2013 – Richard Diogo Jul 15 '15 at 14:14
Not sure what you mean with your comment. The index doesn't change the table or any of it's content. Your not that experienced in using SQL Server, don't you? – Ionic Jul 15 '15 at 14:22
I said I need to test it bcs = external content type sharepoint 2013 [link](http://blogs.msdn.com/b/jysaikia/archive/2010/12/15/a-step-by-step-guide-in-designing-bcs-entities-by-using-a-sql-stored-procedure.aspx) – Richard Diogo Jul 15 '15 at 14:28
Ah ok. Well you can test it. By the way, maybe you need to review the execution plan if there might be some other good thing to try. – Ionic Jul 15 '15 at 15:14

Query optimization on a Table with 1 Million rows

2 Answers2