Completed

Recommend table indexes and/or changes on a SQL Server query. Maybe indexed view.

I have a complex SQL Server query that needs a performance improvement. The query is the return of a function (ie, the function returns the query statement). I think that performance improvements on this query should be: indexes to be created on tables involved and create one or more indexed views (I know there are limitations, specifications and commands to be performed on the database for this, so if indexed view is to be done on this project, all these items need to be specified as well.)

The file for this query is attached.

The best description of the steps that are intended to be performed (as long as the price is good as well) wins this project. (I don´t need the solution on your description, just the prove that "you know what you are talking about" :) ).

Thanks!

Here is an example of the query with the parameters of the file attached provided:

declare @sSQL varchar(max)
set @sSQL = dbo.fnEmailMktSelectQuery3 (
'0', --@idEnvio
'1', --@CodCli
'1', --@eh_contagem_de_tela
'20,16,44', --@selListasEmailIn
'515,517', --@selListasMktIn
'', --@selListasEmailOut
'', --@selListasMktOut
'', --@selOptOut
'0', --@flag_IncluirEnviosAnteriores
'', --@EnvAntMensagemAbertaClicada
'', --@EnvAntDataEnvioInicial
'', --@EnvAntDataEnvioFinal
'', --@EnvAntEnviosEspecificos
'', --@EnvAntProjetos
'', --@EnvAntMensagens
'', --@EnvAntListasEmails
'' --@EnvAntListasMarketing
)
print @sSQL

And the resulting query is:
SELECT '1' as CodCli, isnull(q.CodContato,0) as CodContato, max(q.EmailDestino) as EmailDestino
from ( SELECT max(isnull(u.CodContato,0)) as CodContato, u.EmailDestino, max(u.NomeCompleto) as NomeCompleto
from (SELECT distinct lista_email.NO_CodContato as CodContato, lista_email.NM_Email as EmailDestino, case when isnull(lista_email.NM_NomeDestinatario,'')='' then lista_email.NM_Email else lista_email.NM_NomeDestinatario end collate SQL_Latin1_General_CP1_CI_AS as NomeCompleto
from tblSYSEmailmktListaEmails lista_email with (nolock) where lista_email.NO_CodCli='1' and isnull(lista_email.flag_Excluido,'0')='0' and lista_email.NO_CodLista in (20,16,44)
UNION
SELECT distinct lc.NO_CodContato as CodContato, fc.NM_Conteudo as EmailDestino, con.NM_NomeCompleto collate SQL_Latin1_General_CP1_CI_AS as NomeCompleto
from tblSYSContatos con with (nolock)
inner join tblSYSFormasContato fc with (nolock) on con.ID_Contato = fc.NO_CodContato
inner join tblSYSFormasContatoDisponiveis fcd with (nolock) on fc.NO_CodFormaContatoDisponivel = fcd.ID_FormaContatoDisponivel
inner join tblSYSListasContatos lc with (nolock) on lc.NO_CodContato = fc.NO_CodContato
where con.flag_Ativo='1' and fc.NO_CodCli='1' and fcd.NO_CodCli='1' and lc.NO_CodCli='1' and isnull(lc.flag_Ativo,'1')='1' and fcd.NM_Tipo='email' and isnull(fc.flag_Receber,'1')='1' and isnull(fc.flag_Ativo,'1')='1' and lc.NO_CodLista in (515,517)
) as u
left join
(select lista_email.NM_Email
from tblSYSEmailmktListaEmails lista_email with (nolock)
where lista_email.NO_CodCli='1' and isnull(lista_email.flag_Excluido,'0')='0' and lista_email.NO_CodLista in (0)
) as email_out on email_out.NM_Email = u.EmailDestino
left join (select fc.NM_Conteudo
from tblSYSFormasContato fc with (nolock)
inner join tblSYSFormasContatoDisponiveis fcd with (nolock) on fc.NO_CodFormaContatoDisponivel = fcd.ID_FormaContatoDisponivel
inner join tblSYSListasContatos lc with (nolock) on lc.NO_CodContato = fc.NO_CodContato
where fc.NO_CodCli='1' and fcd.NO_CodCli='1' and lc.NO_CodCli='1' and fcd.NM_Tipo='email' and isnull(lc.flag_Ativo,'1')='1' and isnull(fc.flag_Receber,'1')='1' and isnull(fc.flag_Ativo,'1')='1' and lc.NO_CodLista in (0)
) as email_mkt_out on email_mkt_out.NM_Conteudo = u.EmailDestino
left join (select optout.NM_Email
from tblSYSEmailMktOptOutEspecificoEmails optout with (nolock)
where optout.NO_CodCli='1' and optout.NO_CodOptOutEspecifico in (0)
) as opt_out on opt_out.NM_Email = u.EmailDestino
left join tblSYSEmailMktOptOutGeral optout_geral with (nolock) on optout_geral.NM_Email = u.EmailDestino and optout_geral.NO_CodCli='1'
left join tblSYSEmailmktBouncesGeral bounces_geral with (nolock) on bounces_geral.NM_EmailAddress = u.EmailDestino and bounces_geral.NO_CodCli='1'
where isnull(email_out.NM_Email,'')='' and isnull(email_mkt_out.NM_Conteudo,'')='' and isnull(opt_out.NM_Email,'')='' and isnull(optout_geral.NM_Email,'')='' and isnull(bounces_geral.NM_EmailAddress,'')=''
group by u.EmailDestino ) as q group by q.CodContato, q.NomeCompleto

This freelancer website is cutting the full description, so the TXT file attached has the full description...

Skills: Microsoft SQL Server, SQL

See more: insert table lost connection mysql server query, sql server query tree parent, sql server query rijndael aes net, sql server indexed views pros and cons, sql server create index, sql server index tutorial, sql server index recommendations, sql server indexed view, how to do indexing in sql server 2012, sql server 2012 indexing for performance, sql index, insert table xml field sql server, copy table databases vb6 sql server, create csv file sql server query, open table xml link sql server, save image table vb net sql server, vb net pivot table data source sql server database, create table script generator sql server, create table script generator sql server sql server, aspdotnetstorefront sql server query admin password

About the Employer:
( 21 reviews ) São Paulo, Brazil

Project ID: #17672284

Awarded to:

sumon355

Hello, As an expert in SQL, i am very much interested to optimize your query. I have checked your attached function and it is tough to understand the query this way. Can you please send me the generated query from thi More

$30 USD in 0 days
(180 Reviews)
7.3

17 freelancers are bidding on average $126 for this job

truongnguyen86

Hello there, i've seen your text file, it seems the function is creating the dynamic queries which actually is input some data inside that query for filter, no more. The problem i can see is you're using too much joins More

$250 USD in 3 days
(140 Reviews)
6.5
robnicholson

I work with SQL performance problems like this on a daily basis and can get this sorted. Quick Summary about me: SQL Server Certified Master (highest cert), 15 years experience with small - large organisations, SQL (20 More

$70 USD in 1 day
(24 Reviews)
5.5
$80 USD in 3 days
(28 Reviews)
4.7
$222 USD in 3 days
(11 Reviews)
3.8
idragon712

Hi How are you? I am a senior web developer with 5 years of experience in building the websites. I have high skills in Database management. I see what do you want. I hope we will be able to discuss on your project More

$150 USD in 3 days
(3 Reviews)
3.3
$222 USD in 5 days
(9 Reviews)
3.6
HDDevelopment

dear sir , i have experience in sql server +15 years

$166 USD in 3 days
(4 Reviews)
3.0
$111 USD in 4 days
(0 Reviews)
0.0
$30 USD in 4 days
(0 Reviews)
0.0
$200 USD in 5 days
(0 Reviews)
0.0
Muthu1206

Currently I am working on PLM software development, I well knowledge in sql backend.

$35 USD in 2 days
(0 Reviews)
0.0
srikanthchindam9

Hi, This is Srikanth Chindam, database developer. I am a quick learner and have good analytical and problem solving skills, mostly worked on 1. SQL Server performance tuning 2. Writing complex business logics using More

$100 USD in 2 days
(0 Reviews)
0.0
$155 USD in 3 days
(0 Reviews)
0.0
giorgosbetsos

I will: [1] meticulously study the execution plan of your query and optimize the part that is consuming the most time in query execution. [2] break the query in small pieces and examine the execution plan / time of More

$61 USD in 3 days
(0 Reviews)
0.0
manishsamariya

I have 6 year of experience in database development so i can do this as soon as possible. I have hands on experience in sql server, performance tuning, and database structure. I am goo d in DBMS.

$110 USD in 3 days
(0 Reviews)
0.0
naveen664385

You dont see any reviews in here as i am new to join the site. But i am not new to tuning SQL queries. I wrote so many complex sql queries and tuned very big queries.

$155 USD in 3 days
(0 Reviews)
0.0