Automating SQL queries - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Automating SQL queries

Author

Topic

Topaz
Posting Yak Master

199 Posts

Posted - 2010-10-12 : 06:15:58

I'm wondering if there is a simple solution to my problem?

I have a fairly large SQL database that I use for email marketing purposes.

The main table has lots of different fields but the main ones that I am going to talk about are: uniqueid, website, emailaddress, batch, lastmailed

Lastmailed - is a date field where at the point of sending, gets filled out with the date of email sent.
Batch - is a number field that I use to lookup all the emails in a specific batch. See below for an example query I use to select my emails.

select uniqueid, website, emailaddress, batch, lastmailed
from my_table
where batch like '55'

As you can tell this is a really simple query to find my email addresses. My database is over 1 million records and each batch is split up into 3000 contacts. That equates to over 300 seperate batch numbers. It's beginning to take a lot of my time to send out each batch individually.

I know I could rewrite the query to look like this:

select uniqueid, website, emailaddress, batch, lastmailed
from my_table
where batch like '55' or batch like '56' or batch like '57' or batch like '58'

This would increase the size of lookup and allow me to send out a greater volume of emails a lot quicker. However what I would like to know is would there be any way of automating the creation of the SQL query? To save me lots of time?

JT

kishore_pen
Starting Member

49 Posts

Posted - 2010-10-12 : 06:38:07

you can use between operator in your where clause instead of like.

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2010-10-12 : 06:52:35

Well there a few pointers.

1) LIKE is a pattern matching keyword. if you want to match an exact string use = instead.

So batch LIKE '55' will become batch = '55'

2) for nested batch = '55' OR batch = '56' OR batch = '57' you can replace with an IN clause

So this would become
batch IN ('55','56','57')

If you wanted to return all batch where the first character is '5' you can use LIKE

batch LIKE '5%'

Which will match any string like '5abv#', '5a', '5', '564' but WOULD NOT match any string not starting with a 5.

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

Kristen
Test

22859 Posts

Posted - 2010-10-12 : 06:55:41

You shouldn't really use LIKE unless the comparison data contains a wildcard - unless SQL is smarter than I think! it is liable to use a less efficient query plan to find the records than if you just used "="

You can use BETWEEN as already mentioned, or


where batch IN ('55', '56', '57', '58')

if there are a bunch of batch numbers that you want to send that don't happen to be contiguous.

If [batch] is an integer / numeric datatype IMHO your should remove the single quotes around the numbers - but it may just be that the example you have shown here "happens" to be integers

Why are they split into batches? We have a simialr system, but that is specifically so that we do NOT send out too many in one go - which tends to cause a timeout on the database, or some conflict. If that is the reason you too have batch numbers then you would need to loop around them:


DECLARE @intBatch int,
        @intMaxBatch int

SELECT @intBatch = 55,
        @intMaxBatch = 58

WHILE @intBatch <= @intMaxBatch
BEGIN

    select uniqueid, website, emailaddress, batch, lastmailed
    from my_table
    where batch = @intBatch
    -- Error Checking / Handling required here!!

    SELECT @intBatch = @intBatch + 1
END

Topaz
Posting Yak Master

199 Posts

Posted - 2010-10-12 : 07:09:10

First of all, thank you kishore_pen & Transact Charlie for teaching me to use the = instead of like when it comes to select specific data. That does seem to speed up the query a few seconds.

Kristen

I'm not too sure what the query you knocked up actually does? What is the benefit of looping our batch numbers??

The reason for me splitting our database up into smaller batches is so that we can run reports on each individual batch and analyse open /click through rates. Looking back at how well that batch has performed over a year. (Just a nice way to improve our emailing quality'. Our equipment here is pretty hefty and can cope easily with 60k emails a day - I'm not a spammer before you ask.

JT

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources