Thursday, September 07, 2006
Sunday, September 03, 2006
How to eliminate cursor in SQL Server
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
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.
Tuesday, August 15, 2006
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.
- 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
- 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.
Monday, August 14, 2006
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" >
Page2.Html has
<Img Src=”/company/images/logo.jpg” >
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.
Sunday, August 13, 2006
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.
- 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.
Friday, August 11, 2006
Display when page has been updated
< script language="Javascript" >
document.write("This page was last modified on: " + document.lastModified +"");
< /script >
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.
Monday, August 07, 2006
Convereting your Idea into functional Website
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).
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?
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.