Website Hosting and Development: How to eliminate cursor in SQL Server

Website Hosting and Development: How to eliminate cursor in SQL Server

How to eliminate cursor in SQL Server

We all know how cursors are slow and how badly they tend to issue a lock on part of the table, or on the whole table, during its execution. To explain exactly what is going on, and what we can do to optimize the performance, here is an example:

Table t_Customers has 3000 records.

Table t_Orders has 1 to 100 records for each record in t_Customers table, making it a table with 65000 records.

We want to print all of the orders for customers that had orders in the last month. There are several solutions that we can use and first one is usage of the cursor:

Declare
@nCustID Int
, @cCustName varchar(30)
, @nOrdNo Int

Declare curCusOrd For
Select
C.CustID
, C.CustName
, O.OrderNo
From
t_Customers C
Left Join t_Orders O
On C.CustID = O.CustID
Where
O.OrderDate >= DateAdd( month, -1, GetDate() )

Fetch Next From curCusOrd
Into
@nCustID
, @cCustName
, @nOrdNo

While @@Fetch_Status = 0
Begin

/*
Print Order
*/

Fetch Next From curCusOrd
Into
@nCustID
, @cCustName
, @nOrdNo

End

Close curCusOrd
DeAllocate curCusOrd

In example above, cursor will lock part of each table, or both tables as a whole, while orders are printed. On top of this, a lot of disk activities will be done in order to scan both t_Customers and t_Orderd tables, resulting a very slow performance.

The first alternative is to create a temporary table, that will contain all key columns, and then to use it as a pointer instead of cursor. For a small dataset we can easily eliminate a slow performing and memory intense cursor with a temporary table. Here is a same procedure done with temporary table:

Declare
@nCustID Int
, @cCustName varchar(30)
, @nOrdNo Int
, @nRowCnt Int
, @nRows Int

Create Table #tempCustOrd
(
CusOrdID IDENTITY (1, 1) Primary key Not Null
, CustID Int Not Null
, CustName varchar(30) Not Null
, OrderNo Int
)

Insert #tempCustOrd
Select
C.CustID
, C.CustName
, O.OrderNo
From
t_Customers C
Left Join t_Orders O
On C.CustID = O.CustID
Where
O.OrderDate >= DateAdd( month, -1, GetDate() )

Select
@ nRowCnt = 1
, @nRows = Count(*)
From #tempCustOrd

While @nRowCnt <= @nRows
Begin
Select
C.CustID
, C.CustName
, O.OrderNo
From #tempCustOrd
Where CusOrdID = @nRowCnt

/*
Print Order
*/

Select @nRowCnt = @nRowCnt + 1

End

Drop Table #tempCustOrd

Locks that were issued on t_Customers and t_Orders are now eliminated, but the price is a lot of disk activities, along with the potential locking of whole temporary table. All of this adds up to a lousy performance, especially on large or long-running operations.

The third alternative is to use a SQL Server Table variable. This is a variable that can be used in stored procedures, functions and batches. They have no permanent life outside of the batch that contains them. They're cleaned up automatically at the end of the batch and you don't need to worry about name conflicts with anything outside of this scope. Within its scope, a table variable behaves like a permanent table.

Now lets take a look at our example using a Table variable. It is almost identical as the temporary table example. Only difference is the table declaration and the fact that you do not need to drop it after you're done using it. So here is our code with Table variable:

Declare
@nCustID Int
, @cCustName varchar(30)
, @nOrdNo Int
, @nRowCnt Int
, @nRows Int

Declare @t_CustOrd Table
(
CusOrdID IDENTITY (1, 1) Primary key Not Null
, CustID Int Not Null
, CustName varchar(30) Not Null
, OrderNo Int
)

Insert @t_CustOrd
Select
C.CustID
, C.CustName
, O.OrderNo
From
t_Customers C
Left Join t_Orders O
On C.CustID = O.CustID
Where
O.OrderDate >= DateAdd( month, -1, GetDate() )

Select
@ nRowCnt = 1
, @nRows = Count(*)
From @t_CustOrd

While @nRowCnt <= @nRows
Begin
Select
C.CustID
, C.CustName
, O.OrderNo
From @t_CustOrd
Where CusOrdID = @nRowCnt

/*
Print Order
*/

Select @nRowCnt = @nRowCnt + 1

End

Table variable resides in memory, there is no disk I/O, it does not use tempdb, therefore eliminating all of the locks. Execution is fast; it takes 1/5 of time to complete the same job then it would using cursors.

As you can expect there are limitations:

• Server physical memory. Always keep your server memory in mind when you work with Table variable
• You can not create index on the Table variable. If you need to work with a large amount of data with many indexes, use a temporary table instead of a table variable
  
• The table scheme on Table variable is static. If you need to modify it in the course of a batch, use a temporary table instead.
  
• You can not use Select Into statement on a Table variable
  

As a general rule of thumb, you should use table variable any time that your temporary data is of reasonable size and is only used a few times. As your temporary data grows in size, complexity, and reuse, a temporary table will be more appropriate. Using execution plans, SQL Profiler, and performance counters available to monitor what's going on inside of your stored procedures, you can code the alternatives and test them when there's any doubt.

Happy coding, Marin

---

Cheers, Marin
Check for more articles on this topic at Website Hosting and Development website.

You can also visit DIMM Info Systems Inc. website to see how else I can help you achive your goals.

Design your webpage with visitors in mind. Optimize your images to reduce the load time – Part 3

I know I already beat this topic to death, but I will remind you again, please take care of your web page load time. Folks with slow connection will get frustrated if it takes too much time for your web page to load.

Other than dividing your large image files into smaller chunks and reusing of your images you can optimize your images for faster load times. Currently, the most commonly used image formats are Jpeg and Gif. Each of them can be reduced in size with some simple tricks.

The JPEG file format (Joint Photographic Experts Group) is designed to compress photographs or images with continuous-tone color, such as a color gradient. JPEG format supports 24-bit color (millions of colors) and preserves the broad range of color and subtle variations in hue that characterize continuous-tone image. To optimize Jpeg image you can:

• Use a preview option in your graphic programs to compare difference in final file size with different compression levels

• Implement little blur effect because Jpeg format “likes” images with softer edges, fewer details and fine shades

If your image contains large areas of flat color, sharp detail (such as small type), or if you need to preserve true transparency, you should use the GIF file format. To optimize GIF image you can:

• Reduce a bit depth in image. Gif image supports up to 256 colors. Many images can be displayed with far less color, without sacrificing the quality.

• Crop all unnecessary white areas in the background. Gif format supports transparency, so use it freely!

One general advice for all web page image types: Reduce the size of your image (if you can of course) The smaller the image, the faster it loads.

...and stillmore to come...

Design your webpage with visitors in mind. Optimize your images to reduce the load time – Part 2

As I tried to point in the previous post, keep in mind your web page load time whenever you design your web page. If your visitor is on dial-up connection, waiting for your page to load can produce an empty screen for 10 or more seconds. That would be enough for them to give up and forget your site.

The physical location of your website images is important. Usually, overseen mistake is usage of same image files from different locations on your website. Check following example with logo.jpg image:

Page1.Html has
<Img Src-="/images/logo.jpg" &#62


Page2.Html has
<Img Src=”/company/images/logo.jpg” &#62

Example like above can be even worse if you have the same image on several different locations because you are not reusing your images. If your images are centrally located and only a single copy exists for all of them, (ALL OVER YOUR SITE, not just each page individually) each image is loaded only once when visitor hits your site first time. All other instances of the same image are loaded from visitor’s machine cache, producing less network traffic (which takes time, especially on dial-up connection) and faster web page load.


More to come…

---

Cheers, Marin

Check for more articles on this topic at Website Hosting and Development website.

You can also visit DIMM Info Systems Inc. website to see how else I can help you achive your goals.

Design your webpage with visitors in mind. Optimize your images to reduce the load time

As you may know all successful web sites are designed with visitors in mind. There are way too many website elements that can (and should) be customized in order to make you visitors happy. Website content, the amount of relevant and accurate information, eye catchy design, incorporated audio and visual effects are just some of the mentioned elements. These are well discussed topics and a lot of professionals can argue about each of them, what they should and should not to do and how to avoid common traps and mistakes.

One of usually omitted topic is your webpage load time. With fast Internet connection that should not be a big problem, but most of us tend to quickly forget people that are still using dial-up. If your webpage is graphically rich, and your graphics are not well optimized, it might be a frustrating experience for your visitors with dial-up. What should you do to speed up your webpage load time? There are several things that you can change that will boost your webpage load performance. One of them is quick and easy to implement (if it is not already done):

• Divide your large image into several smaller images and bring them together. By doing this, instead of one large data stream that loads your single image, you will have several small data streams that are loading simultaneously.

More on this topic in following posts…

---

Cheers, Marin
Check for more articles on this topic at Website Hosting and Development website.

You can also visit DIMM Info Systems Inc. website to see how else I can help you achive your goals.

Display when page has been updated

It is a good idea to show when a webpage has been last updated. By doing this, your visitors will get the idea on how often new content is added to your website and will want to come back. You can accomplish this by adding a following JavaScript code in your webpage code:

< script language="Javascript" &#62
document.write("This page was last modified on: " + document.lastModified +"");
< /script &#62

Instead of JavaScript you can use php code below to accomplish same result:

<?
header("This page was last modified on: " . gmdate("D, d M Y H:i:s",
filemtime($SCRIPT_FILENAME)) . " GMT");
?>

---

Cheers, Marin
Check for more articles on this topic at Website Hosting and Development website.

You can also visit DIMM Info Systems Inc. website to see how else I can help you achive your goals.

Convereting your Idea into functional Website

Website Idea, Creativity, Brainstorming...

So you have a GREAT Idea for your new Website, but how to do it?

Your business idea is essential. You set your goals, define and follow all the steps required to accomplish them and results are there. Now is it the same whenyou want to do it on the web? Well, there are several answers to it, each dependson many different factors, such as scale of your website idea, how soon you want tot publish it, what is your budget… Combinations are endless. Each has its own path, some are short and easy, some are complicated with almost impossible realization, but most are somewhere in between.

I believe that it is really rare to have a good website idea that is impossible to realize. It all depends on proper planning, so let's start with it.

You can think of your Idea as pyramid, you are at the base of it, with lot of widespread options. At the top is your goal, your website idea that should be realized. If you look at this pyramid as an Egyptian monolith structure you might get scared,but if you look closer you will realize that this is more Aztec stepped pyramid, with stairways following sure path from the wide base to the top. From now on we will have Aztec pyramid on our mind.

Start planning from the top and realization from the bottom.

You need to have well defined process. Put your main goal at the top of your pyramid. Now move one step below. Which steps do you need to accomplish in order to get to the top? That will be your intermittent goals. Now think about each of them as a small pyramid. Each of them has its goal at the top and you have to move another step below. Break it into smaller steps and do it as long as you need to get all stepson the level that is easy to accomplish.

When you have defined your plan you can start with realization. This could be tedious and exhausting process. To succeed you needs a strong quality control and assurance. Once when you have all your small pyramids aligned start from the base. Spread feely all that you have, regardless if it is directly related to your idea or not. Spend some time and be creative, let the creative juices flow, let sparks fly!

Next step in realization is to see what elements that you have can be used to realize your first level of goals. In short the whole process can be represented as the following:

• Identify your goal
• Set quality standards for each level. Higher level should inherit accomplished quality from lower level
• Recognize interim steps that lead to accomplishing stepped goals
• Complete all steps on the lowest - widest level of your pyramid
• After finishing each level of interim goals check if they fulfill your project quality, if not, return one step below and improve steps that failed below your quality standards
• Go to the higher level when all goals from lower levels have been accomplished with quality standards As you can see, the process is not that complicated. If you define all steps required and follow the path you will be there (sooner or later).

Good Luck!

---

Cheers, Marin
Check for more articles on this topic at Website Hosting and Development website.
You can also visit DIMM Info Systems Inc. website to see how else I can help you achive your goals.

Website Hosting and Development - What is this blog about?

A Website is your Home on the Internet. Building it is the same as building any new business; it can be very simple, fast and cheap. But it can be very tedious, time consuming and expensive too. With a simple, fast and cheap solution that is exactly what you are going to get, A CHEAP website that will not serve a purpose, won't attract potential users to come again. Sooner or later it will become your hobby site that will not bring any visitors. On the other hand, if you aim way too high you can spend months in planning, development and design, a big deal of money can run out of your pocket and, at the end you will (probably) get what you want. In that case, you need experienced people that can put things together, manage this project with maximum effectiveness and keep your expenses on an acceptable level.
I will try to follow a smooth path, which will explain each individual step and procedure, give you a clear guideline on how to accomplish all your goals and to tell you what follows.
Watch for my new posts on this blog and you will get a valuable info that you can count on.