tag:blogger.com,1999:blog-42827428556365286492024-02-20T16:19:04.669-08:00An Awfully Big SQL AdventureSQL Server and .Net ExcitementScott Dickens-Cookhttp://www.blogger.com/profile/01629275141615529863noreply@blogger.comBlogger9125tag:blogger.com,1999:blog-4282742855636528649.post-12676190233028237712015-03-23T14:53:00.000-07:002015-03-23T14:57:33.713-07:00Parallelism and a big performance difference between temp tables and table variablesInteresting challenge the other day while working on my <a href="http://www.play-free-games.com/free-games/learn-french/vocab.aspx">French Vocab Game</a>, "French Tiger".<br />
<br />
I needed to create a table variable to hold some data for use later on within a more complex query. Running the table variable declaration and the select statement was very fast (less than one second). Not surprising because the number of results returned was typically small (0-50) and the "French Word" column was nicely indexed.<br />
<br />
<span style="color: #3d85c6;"><span style="background-color: white;"><span style="font-family: "Courier New",Courier,monospace;">DECLARE @p TABLE (Id int PRIMARY KEY)</span></span></span><br />
<span style="color: #3d85c6;"><span style="background-color: white;"><span style="font-family: "Courier New",Courier,monospace;">SELECT Id FROM Vocab WHERE FrenchWord LIKE @Value + '%'</span></span></span><br />
<br />
However, when I added the simple step of inserting those 50 rows in to my table variable, the operation slowed down to 2 seconds.<br />
<span style="color: #666666;"><br /></span>
<span style="color: #3d85c6;"><span style="font-family: "Courier New",Courier,monospace;">DECLARE @p TABLE (Id int PRIMARY KEY)</span></span><br />
<span style="color: #3d85c6;"><span style="font-family: "Courier New",Courier,monospace;">INSERT INTO @p</span></span><br />
<span style="color: #3d85c6;"><span style="font-family: "Courier New",Courier,monospace;">SELECT Id FROM Vocab WHERE FrenchWord LIKE @Value + '%'</span></span><br />
<br />
This seemed bizarre – how could such a simple operation take so long? I use this approach in many places to help optimize the elements of complex queries, and I had never seen anything like this before. After spending hours experimenting with different indexes and minor adjustments to the query (not easy to come up with because it is so simple) I finally found an answer that brought the speed of the operation back down to zero seconds. The answer was to use a temporary table instead of the table variable:<br />
<br />
<span style="color: #3d85c6;"><span style="font-family: "Courier New",Courier,monospace;">INSERT INTO #p</span></span><br />
<span style="color: #3d85c6;"><span style="font-family: "Courier New",Courier,monospace;">SELECT Id FROM Vocab WHERE FrenchWord LIKE @Value + '%'</span></span><br />
<br />
I wasn’t happy to discover this solution because I have always felt drawn to favour the table variable in most cases where there is a choice between a table variable or a temp table (because of not having to clear up after it). It seems the key reason that in this particular circumstance the temp table was superior is that any query including a table variable cannot benefit from parallelism. My server has numerous CPUs, and it seems that for whatever reason it was necessary to have several of them involved in order to run this insert quickly.<br />
<br />
In order to prove that parallelism was the deciding factor I ran the temp table version again with the use of<br />
<br />
<span style="color: #3d85c6;"><span style="font-family: "Courier New",Courier,monospace;">option (maxdop 1)</span></span><br />
<br />
… to effectively switch off parallelism, and bam! – back to 2 seconds for my insert.<br />
<br />
Lesson learned for this table variable fan – always have to consider the alternative.Scott Dickens-Cookhttp://www.blogger.com/profile/01629275141615529863noreply@blogger.com0tag:blogger.com,1999:blog-4282742855636528649.post-44949136167487655822014-05-16T14:36:00.000-07:002014-05-16T14:36:50.579-07:00HTML5 Audio in Games For iPad and iPhoneI have been writing an <a href="http://www.play-free-games.com/free-games/word-games/play.aspx">HTML5 word description game</a> called "Word Boomer" for use on tablets and smartphones.<br />
<br />
It was all working beautifully until I came to the seemingly minor task of adding some sound, which I left until last. "This should be easy," I thought, "thanks to the funky new <audio> tag in HTML5."<br />
<br />
For the third time that day, I was wrong, for two reasons:<br />
<br />
1) Many browsers for mobile devices choose not to support automatically playing a sound on page load.<br />
<br />
2) Similarly, many mobile browsers will only allow a single audio tag per page - but my game needed two different sounds.<br />
<br />
Problem #1 was easy enough to overcome by adding an otherwise pointless "Start Game" button for the user to click. The mobile browsers responded just fine as long as a deliberate action was taken by the user to initiate playing the sound.<br />
<br />
Problem #2 was trickier. The best solution I found was two work with just one <audio> tag but switch its source programatically using Javascript when I needed to change from one sound to another.<br />
<br />
Must admit there is a tiny bit of lag between the two sounds at times, but overall it works acceptably on iPhone, iPad and a variety of Android devices. Here's the code: <span style="font-family: "Courier New",Courier,monospace;"> </span><br />
<blockquote class="tr_bq">
<span style="font-family: "Courier New",Courier,monospace;">var aud = document.getElementById('FuseSound1');</span><br />
<span style="font-family: "Courier New",Courier,monospace;">aud.pause();</span><br />
<span style="font-family: "Courier New",Courier,monospace;">aud.src = document.getElementById('BombSound').src;</span><br />
<span style="font-family: "Courier New",Courier,monospace;">aud.load();</span><br />
<span style="font-family: "Courier New",Courier,monospace;">aud.loop = false;</span><br />
<span style="font-family: "Courier New",Courier,monospace;">aud.play();</span></blockquote>
Oh, oh, oh, it's magic.Scott Dickens-Cookhttp://www.blogger.com/profile/01629275141615529863noreply@blogger.com0tag:blogger.com,1999:blog-4282742855636528649.post-6231407795403516192013-03-20T14:25:00.001-07:002013-03-22T15:54:56.138-07:00SQL Server Connection TimeoutI had a problem recently on <a href="http://www.play-free-games.com/">play-free-games.com</a> whereby I was hitting connection timeouts while connecting to SQL Server 2008 from my .Net application using ADO.<br />
<br />
I would stress the point that these were connection timeouts rather than command timeouts. In many cases the timeout occurred when calling SqlConnection.Open(), without even defining a command to execute.<br />
<br />
What surprised me (I'm easily surprised) was that the timeouts were almost instantaneous - within 1-2 seconds. SQL Server connection strings include a parameter called "<b>Connection Timeout</b>", which I had not set, but the default is listed as 15 seconds.<br />
<br />
I wasted a lot of time asking network nerds to trace the connection, check for dodgy switches etc, only to find a Microsoft blog post stating that in some circumstances the timeout applied will only be 8% of what is specified if your SQL server is mirrored:<br />
<br />
<span style="color: #1f497d;"><a href="http://msdnrss.thecoderblogs.com/2011/05/ado-net-application-connecting-to-a-mirrored-sql-server-database-may-timeout-long-before-the-actual-connection-timeout-elapses-sometimes-within-milliseconds/" target="_blank">http://msdnrss.thecoderblogs.<wbr></wbr>com/2011/05/ado-net-<wbr></wbr>application-connecting-to-a-<wbr></wbr>mirrored-sql-server-database-<wbr></wbr>may-timeout-long-before-the-<wbr></wbr>actual-connection-timeout-<wbr></wbr>elapses-sometimes-within-<wbr></wbr>milliseconds/</a></span> <br />
<br />
With this in mind I increased the timeout value in my connection string to 200, and the problem disappeared except at peak usage times.<br />
<br />
I then added a "<b>Min Pool Size</b>" setting of 30, and the problem disappeared altogether.<br />
<br />
Top hole!Scott Dickens-Cookhttp://www.blogger.com/profile/01629275141615529863noreply@blogger.com0tag:blogger.com,1999:blog-4282742855636528649.post-56924351090753575402013-02-21T13:21:00.000-08:002013-02-21T13:23:42.871-08:00Multiple Column Primary Keys on Table VariablesI luuurrrve table variables in SQL Server - in fact they are my third favorite thing after otters and custard.<br />
<br />
I actually used them for a couple of years without realizing that I could define a primary key, boosting performance:<br />
<br />
DECLARE @Cakes table (CakeId int PRIMARY KEY, CakeName varchar(30), HasFrosting bit)<br />
<br />
For some of my recent work on <a href="http://www.giantpandaplanet.com/">Giant Panda Planet</a> I found I needed my table variable to have a multiple column primary key... was it an impossible dream?<br />
<br />
Not really:<br />
<br />
DECLARE @PandaFriends table (<br />
PandaId int,<br />
FriendId int,<br />
IsBestFriend bit,<br />
PRIMARY KEY(PandaId, FriendId)<br />
)<br />
<br />
Side Pocket!Scott Dickens-Cookhttp://www.blogger.com/profile/01629275141615529863noreply@blogger.com0tag:blogger.com,1999:blog-4282742855636528649.post-38753571435138178632011-05-04T06:44:00.000-07:002011-05-04T06:46:12.242-07:00Problems Using GIF Images In SilverLightWhen first getting to grips with Silverlight, I had some problems with images.<br />
<br />
I was creating a new Silverlight game for <a href="http://www.play-free-games.com/">Play-Free-Games.com</a> ("Panda Football" - yet another variant on the same old "Panda's playing football" theme one sees so often), and I was ready to start showing my Panda GIF images on screen.<br />
<br />
No matter what I tried, the images were not displayed. I figured I must be specifying the path incorrectly, so I went through a long and tedious process of trying every possible relative or absolute method for defining the image path. I was on the point of tears when I tried displaying another image I happened to have to hand (a picture of a sheep - no further details required). To my intense joy, the image loaded up exactly as expected.<br />
<br />
My initial assumption was that SilverLight does not support images of pandas, only farmyard animals. I was about to put this to the test by trying some goat, duck and chicken images when I realised that there was another key difference - my sheep pic was a jpg.<br />
<br />
"Surely the problem isn't simply that Silverlight does not support the GIF format", I thought to myself, but not for the first time that day I was so wrong:<br />
<br />
<a href="http://forums.silverlight.net/forums/p/39235/112333.aspx">http://forums.silverlight.net/forums/p/39235/112333.aspx</a> <br />
<br />
So, I converted my GIFs to PNGs and all was well. Cashback!Scott Dickens-Cookhttp://www.blogger.com/profile/01629275141615529863noreply@blogger.com0tag:blogger.com,1999:blog-4282742855636528649.post-73287656247392061652011-03-13T14:24:00.000-07:002011-03-13T14:24:06.140-07:00Importing data into SQL Server from a text file using BULK INSERTAs a .Net / SQL Server developer it is often necessary to import data in to your databases from other sources, such as text files. Ideally you would leave this type of data manipulation activity to the kind of weirdos who enjoy it (DBAs). Sadly however this is not always possible.<br />
<br />
Therefore, in the old days of SQL Server 2000 I often created DTS packages to suck in my data. DTS was OK, especially the funky grey arrows, but it was a little time consuming. Then - disaster - DTS was phased out in favour of SSIS. At No Frills Corp our DBAs have not been able to give me a server that SSIS actually works on, so I was a bit stuck.<br />
<br />
It was then that I came across the beatifully simple SQL command, BULK INSERT.<br />
<br />
Just point a BULK INSERT command at your text file, tell it where to put the data, and you are all set. It can't be that easy can it? Yes, it really can:<br />
<br />
<pre>BULK INSERT Chimps
FROM 'D:\primates\chimps.csv'
WITH
(
FIELDTERMINATOR =',',
ROWTERMINATOR ='\n'
)
</pre>Cashback!Scott Dickens-Cookhttp://www.blogger.com/profile/01629275141615529863noreply@blogger.com0tag:blogger.com,1999:blog-4282742855636528649.post-69652689495096684092011-03-04T13:39:00.000-08:002011-05-04T06:47:27.499-07:00Finding the active node in a SQL Server ClusterDBAs are an eccentric breed aren't they?<br />
<br />
At my place of work, No Frills Corp, we have 2 types of DBA: DBAs who are perpetually on the edge of a nervous breakdown and will happily go a whole day without saying anything except "No", "I'm busy" and "Must be a problem with your app", and DBAs whom you should not engage in the simplest of conversations unless you have a couple of hours to spare.<br />
<br />
I much prefer the taciturn DBAs, but in both cases the best policy is not to talk to them unless you really have to. So, what if you just need to know something simple but important, like which node of our active-passive cluster is currently active?<br />
<br />
The other day I came across the very simple answer to this question:<br />
<br />
<span style="font-family: "Courier New", Courier, monospace;">SELECT ServerProperty('ComputerNamePhysicalNetBIOS')</span><br />
<br />
<span style="font-family: inherit;">Bosh!</span>Scott Dickens-Cookhttp://www.blogger.com/profile/01629275141615529863noreply@blogger.com0tag:blogger.com,1999:blog-4282742855636528649.post-83754342734344622482011-02-28T14:31:00.000-08:002013-02-20T07:04:12.976-08:00Dormant Full Text Index in SQL ServerI recently created a full text index to improve search performance on some large text fields. The resulting performance was great, so I took it to show my boss (an IT "Bruno").<br />
<br />
I had to wait a few minutes before I could see him, and then when I fired up my lovely new search page disaster struck in the form of a timeout - making me look like an imbecile.<br />
<br />
I covered by showing him something shiny, and hastily hit refresh. The search came back in less than a second, as did each subsequent search. Happy boss.<br />
<br />
When I got back to my desk I did another search and got another timeout. It seems my Full Text Index is going to sleep if not used for 5 minutes, and then takes 30 seconds or more to wake up. Rubbish!<br />
<br />
So, I have had to set up a job to hit the FTI with a simple query every 5 minutes, keeping it "awake".<br />
<br />
Something like this:<br />
<br />
<span style="font-family: "Courier New", Courier, monospace;">SELECT * FROM Dreams WHERE CONTAINS(SubText, 'fear of trombones')</span><br />
<br />
Doesn't feel great having to do that, but now my Full Text Index is always ready for action.Scott Dickens-Cookhttp://www.blogger.com/profile/01629275141615529863noreply@blogger.com2tag:blogger.com,1999:blog-4282742855636528649.post-25784500506886202552011-02-28T14:08:00.000-08:002013-02-20T07:06:06.145-08:00Optional Search Parameters in SQL Server Stored ProceduresI often create Stored Procedures in SQL Server for searching - but what is the best way to handle optional search parameters?<br />
<br />
In the old days I wrote queries like this: <br />
<br />
<span style="font-family: "Courier New", Courier, monospace;">SELECT *</span><br />
<span style="font-family: "Courier New", Courier, monospace;">FROM Chimps</span><br />
<span style="font-family: "Courier New", Courier, monospace;">WHERE (@Age = 0 OR Age = @Age)</span><br />
<span style="font-family: "Courier New", Courier, monospace;">AND (@Weight = 0 OR Weight = @Weight)</span><br />
<br />
... defaulting @Age and @Weight to zero if not specified.<br />
<br />
However, I later found that this performs a lot better:<br />
<br />
<span style="font-family: "Courier New", Courier, monospace;">SELECT *</span><br />
<span style="font-family: "Courier New", Courier, monospace;">FROM Chimps</span><br />
<span style="font-family: "Courier New", Courier, monospace;">WHERE Age = ISNULL(@Age, Age)</span><br />
<span style="font-family: "Courier New", Courier, monospace;">AND Weight = ISNULL(@Weight, Weight)</span><br />
<br />
... with the params defaulting to NULL if not specified. Lovely.<br />
<br />
But wait! What if there is a chance that some of our chimps have a NULL value for Age or Weight? Then we would have to go a bit further:<br />
<br />
<span style="font-family: "Courier New", Courier, monospace;">SELECT *</span><br />
<span style="font-family: "Courier New", Courier, monospace;">FROM Chimps</span><br />
<span style="font-family: "Courier New", Courier, monospace;">WHERE ISNULL(Age, 0) = COALESCE(@Age, Age, 0)</span><br />
<span style="font-family: "Courier New", Courier, monospace;">AND ISNULL(Weight, 0) = COALESCE(@Weight, Weight, 0)</span><br />
<br />
This still performs well in most of my scenarios, but let's face it - ideally our chimps will not have a NULL weight. Weigh those chimps!Scott Dickens-Cookhttp://www.blogger.com/profile/01629275141615529863noreply@blogger.com0