Álcool ou gasolina?:Especial - Economia - Setores - Estadão.com.br

Split e join de strings em T-SQL

Olá,
Um das maiores necessidades dos desenvolvedores que trabalham com T-SQL é o de passar uma string com separadores como parâmetro em uma stored procedure, por exemplo, e usá-la como argumento em uma cláusula where como um conjunto, utilizando a palavra reservada IN.
A necessidade inversa também é importante, ou seja, concatenar o resultado de um campo de uma select em uma string separada por um determinado delimitador.
Estas tarefas, tão comuns em qualquer linguagem de programação, são também conhecidas como split e join.
Para executá-las em T-SQL, existem várias maneiras e muitos já postaram milhares de modos, no entanto, segue minha contribuição que creio ser uma das mais simples e eficientes pois usa dos recursos de XML do T-SQL para seu fim.
Para fazer o split:
DELCARE @string VARCHAR(200)
SET @string = '124, 456, 789, 121212, 3256 '
DECLARE @xmlAuxiliar XML
SET @xmlAuxiliar = '' + REPLACE(@string, ',', '') + ''
SELECT x.i.value('.', 'INT') FROM @xmlAuxiliar.nodes('//i') x(i)
Observação: pode-se substituir o INT da última instrução por outro tipo, como VARCHAR. Neste caso a lista seria algo como 'João, Pedro, Marcos, Lucas'
Para fazer o join:
SELECT NomeDoCampo + ', '
FROM NomeDaTabela
FOR XML PATH('')
Observação: seu separador pode ser outro que não ', '. Caso o retorno do campo não seja um tipo caractere, é necessário converter.
Abraços e até a próxima.

JP dotNet: Split e join de strings em T-SQL

Use following form to generate backup ftp script :)

FTP MySQL and Webserver System Backup script

Use following form to generate backup ftp script :)

FTP MySQL and Webserver System Backup script

Há tempos foi reportado que num dos nossos clientes da nossa solução de pré/auto-venda, o carregamento de uma lista com 60 a 70 registos demorava mais de 5 minutos, enquanto noutros clientes, com o mesmo volume de informação não demorava mais de uns breves segundos. A operação em causa dependia de uma consulta à base de dados no PDA que continha múltiplos JOIN, sobre um comando preparado, e a diferença entre clientes também poderia estar relacionada com os equipamentos utilizados, pois a tal operação de ‘5 minutos’ com a mesma base de dados demorava ‘apenas’ 40 segundos no meu emulador.

Para fazer o tunning à instrução, abri o SQL Server Management Studio, e estudei o Execution Plan da mesma, o que me foi dando ideias para índices a criar, mas sem que tal tivesse um impacto significativo em termos de performance. Aí vi que tinha que mudar a instrução e comecei a pensar como fazê-lo, pois era importante obter a informação das tais tabelas ligadas de uma só vez, mas identifiquei uma tabela de lookup, e tirei-a da instrução SQL, menos um JOIN portanto, e automaticamente o comando passou a ser executado quase instantaneamente. Como a tal tabela era importante, decidi pré-carregá-la numa colecção do tipo key, value, e a cada registo consultava essa colecção para recuperar o valor que necessitava.

Tudo embalado de novo (alteração ao comando, carregamento da lista e novos índexes), no PDA do cliente a tal operação que demorava mais de 5 minutos passou a ser executada em entre 1 e 2 segundos!

Hoje dei com esta pérola no fórum da MSDN dedicado ao SQL Compact, um tipo que tinha uma instrução tão simples como:

SELECT TOP(1) SysDate FROM Violator ORDER BY SysDate Desc

em que a tabela em causa tinha um index criado para a coluna em causa com ordem descendente. Ao executar a instrução no emulador demorava cerca de 35 segundos, o que para ele era inaceitável, e ao analisar o Execution Plan do comando no SQL Server Management Studio, concluíu que o SQL Compact estava a fazer um INDEX SCAN em vez de um INDEX SEEK, que se esperaria que fosse mais performante. O EricEJ, um MVP de SQL Server Compact, sugeriu-lhe uma pequena modificação à instrução, acrescentando-lhe um WHERE inócuo…

SELECT TOP(1) SysDate FROM Violator WHERE SysDate < GETDATE() ORDER BY SysDate Desc

… e automaticamente o comando passou a ser executado de forma muito mais rápida no emulador!

Conclusão

O Query Processor (QP) do SQL Compact tem o seu quê de feminino, é difícil compreender as suas decisões, e mesmo quando achamos que o conhecemos bem, lá aparece uma situação nova a mostrar-nos o contrário, e temos de usar de truques para o convencer a fazer o que queremos :)

We follow these principles:

Our highest priority is to satisfy the customer
through early and continuous delivery
of valuable software.

Welcome changing requirements, even late in
development. Agile processes harness change for
the customer's competitive advantage.

Deliver working software frequently, from a
couple of weeks to a couple of months, with a
preference to the shorter timescale.

Business people and developers must work
together daily throughout the project.

Build projects around motivated individuals.
Give them the environment and support they need,
and trust them to get the job done.

The most efficient and effective method of
conveying information to and within a development
team is face-to-face conversation.

Working software is the primary measure of progress.

Agile processes promote sustainable development.
The sponsors, developers, and users should be able
to maintain a constant pace indefinitely.

Continuous attention to technical excellence
and good design enhances agility.

Simplicity--the art of maximizing the amount
of work not done--is essential.

The best architectures, requirements, and designs
emerge from self-organizing teams.

At regular intervals, the team reflects on how
to become more effective, then tunes and adjusts
its behavior accordingly.

Referencia: http://agilemanifesto.org/principles.html

http://tech-buzz.net/2006/05/11/rapidshare-unleashed-hack-to-download-limit/

1. Va para o site do Google: www.google.com.br
2. Digite: site:rapidshare.de
3. “inurl:pdf” para E-books em formato PDF
4. “inurl:avi|wmv|mpg|nva” para Filmes
5. “inurl:mp3|ogg|wma” para arquivos de Audio
6. “inurl:exe” para arquivos executaveis
7. “inurl:zip|rar|7zip|tar” para RAR, ZIP, 7ZIP ou TAR

1. entre no site do google: www.google.com.br
2. Digite: filetype:torrent <nome>, onde <nome> seria o nome do arquivo torrent que voce esta procurando.
3. Clique no botao procurar.

Randy Dyess of Solid Quality Mentors had a session and since I'd just gotten a copy of Grant Fritchey's book with the same name, I wanted to check it out.

There are 3 types of plans: graphical, text, and XML.

Graphical is cool, looks good, but it doesn't have enough information and once you are in a non-trivial situation, you have to scroll around and it's hard to hold all that information in your mind.

Text plans are what you need to learn to read. Especially in grid mode, we deal with this format for data all the time, so this makes lots of sense for many DBAs, so spend some time working on this. Once you get used to reading text plans, likely this will be your preferred method.

XML - Also hard to read, perhaps need to know XQuery, might be worth learning if you want to know more about XML. Randy doesn't like this, but I think it's worth reading about XML a bit and learning how it works since I think we'll have this data around for a long time. However reading XML isn't easy as things lik whitespace and other characters are encoded. Therefore it's not "human readable" and needs to be consumed by something. However XQuery should help here.

All plans can generate estimated or actual plans. Sometims you can't get actuals since it would be hours to execute the query, or you can't run it on the production system and need to know, or have a good idea of what's happening. Estimated plans help here, but they are not guarenteed to be the same as the actual plan.

One problem with estimated plans is temporary tables. You might need to comment one out to get the plan, and it might not be a good representation of the final plan. If you create a permanent work table and remove it, the same thing happens.

Text Plans

In the text plans, follow the parent column in your plan. It contains numbers, and the highest number happens first. The recommendation is to start tuning with the highest numbers, tuning there first since a change there will flow down to lower numbers. An observation from Randy that SQL Server seems to do the hardest or more resource intensive things first.

Options:

• STATISTICS PROFILE ON
• SHOWPLAN_ALL 
• SHOWPLAN_TEXT
• STATISTICS IO
• STATISTICS TIME ON

Statistics IO gives you IO statistics from the query. You get more informaiton in 2008 as this has been enhanced. This is really read and scan information that you can use to determine where you might think about tuning the query. A big thing here is to try and reduce physical reads, but you need to run this a few times and be sure that the physical reads aren't the first ones to populate the cache. Once the data is in the cache, it might perform much better.

In this case, Randy looks to reduce logical reads. If those can be reduced, even if the data is not in cache, physical reads might be reduced.

Execution plans can also be pulled from memory. You can grab the plan from the cache, which is actually what is being used by SQL Server to execute the query. DMVs are available to help here.

How do you tune?

Randy's Goals

• Optimize duration / CPU usage
• Optimize IO usage

Tracking duration meaning physical clock time, helps to determine how busy the system is. It's good to know an average time for procedures to execute as a baseline. Know that this will grow over time (potentially) As you add data.

CPU usage - Track this since it's a limited resource. Knowing the top usage queries can allow you to focus your efforts on those queries that are used often and impact the CPU.

Large, complex queries usually have large batches of code, each of which can be tuned as a mini-execution plan.

Top Slowdowns

• Table and clustered index scans
• Index scans v index seeks
• Bookmark lookups
• Join methods
• Sorts
• Compute Scalar

Table and clustered index scans are the leading cause of bad performing queries. Not always, sometimes you want this, but it is often bad. A CI scan is a table scan really, but could have better performance since you don't have as many random IOs as on a heap. Randy has this is the number one thing to tune when he sees this. If he can remove this, it often helps. Partial scans are sometimes ok, such as scanning a set of data all grouped together.

Index scans are beter than table scans, but still costly. This usually means a lot of rows ned to be read for the query. Investigate to see if you can turn these into seeks. CI seeks are the best things to have since they hit the data, but only specific rows.

Bookmark Lookups - A non-clustered index is used, but then you must use the CI key to go read the CI and get the data. In 2005 the INCLUDE columns can help remove these. In 2005/2008, you don't see this operator. You see RID (row identifier) and then a Lookup to get the data. These are hard to resolve, you might include 3-5 columns, but not more. You want to review the columns used by indexes to see if you can cover more queries. Or perhaps see if you really need to return that column and perhaps remove the columns. Don't SELECT *, that can cause this. You might also try creating aditional indexes  to be used for joins or perhaps, if appropriate for that table, change the NCI to a CI. This shouldn't be done lightly, especially if you've spent time considering what the best CI should be.

Be sure that your NCI doesn't duplicate the CI keys. They are already included.

Join Methods - SQL Server uses 3 types. Nested loop, Merge, Hash.

Nested loops are for smaller inputs, and has low memory usage. When you have a small and large tables, this often occurs. You don't want this between two large tables. Nested loops may resort your data.

If you have two large tables, a merge join in better. It's for larger inputs, and middle in terms of memory being used.

The hash join is for large tables, uses lots of memory. A row from the first table is run through a hash algorithm and then stored. The hash buckets are stored in MemToLeave, which is only 256MB on 32 bit machines. Above that, the hash tables moves to tempdb, which is much slower.

Sorts are expensive. Don't add them unnecessarily. DISTINCT, UNION, ORDER BY, GROUP BY, aren't always needed. Don't add them out of habit. UNION ALL does not produce a sort because duplicates aren't removed. If possible, allow indexes to presort things.

Compute Scalar is a function being used by the optimizer. Try to reveiew for implicit functions and watch if you have this in a WHERE clause

SQL Server Central