Categories
Database MS SQL Server SQL

Case Sensitive Search in SQL Server

Mysql mostly provide a Case sensitive search because of its default collate configuration. However SQL server use different collate and hence by default it is not case sensitive. It is good to not having case sensitive search by default, as except password compare you most often doesn’t need case sensitive search. Do you? well I never really need that, anyways. Those who wonder how they can get case sensitive search should look this good article

http://blog.sqlauthority.com/2007/04/30/case-sensitive-sql-query-search/

It simply case to change the collate value of column for which you want to get case sensitivity, by defining COLLATE Latin1_General_CS_AS to field declaration.

You can check existing collate of each field using 

EXEC sp_help [tablename]
Alter table [tablename] Alter Column Password Varchar(32) COLLATE Latin1_General_CS_AS

However why are you wondering for case sensitive for password field? aren’t you doing MD5 or other conversion on your password for security already ? :). 

Categories
.NET ASP.NET C#.NET Concept/algorithm Database VB.NET/VB

Static Object in ASP.NET

Recently I found a problem in one of application we developed. Here my colleague use a Static SqlConnection object through a class in his code. He did that to save his effort of writing and Connection everytime. Everything works great during our development and online reviewing. However as soon as we lauch website in beta mode, it start creating problem which seems bit random in occurance and hence were hard to track. During this time I get involve in the project development and I was not very big fan on Static object and start to think against that code, which looks good to me at first. However for some reason I miss the basic of C# and ASP.NET and hence it took a while to recognize the problem was indeed the static code. So for those who did same mistake here is explanation of why Static object didn’t work in ASP.NET for connection.

When ASP.NET application is first called the complete code is loaded in Memory, specially the pages in App_Code folder and they act as part of web server and not as standard PHP site where they run and loaded on each request. Hence, once this happens all static object become static in memory for each and every request your server will get. This is indeed good way to share information between all User and all request, if that help your case. But in most general case you never want to share information between user in this dynamic method, which is not permanent. Anyways, for connection it really doesn’t make a difference if you share the object as you anyways want to open connection and close it as soon as you are done. In most case you have 1 simple insert or fetch query to run. And in a website with several users at a time it hardly make a difference as Sqlconnection are open and close in jiffy and took less than 1 second in most cases. For a shopping cart which has 100 -200 order per month this case will work just fine.

However, since connection is shared, there is every possible chance that if you fire a SQL query for say Reader, it get shared with some user on some other session and this is what you don’t want. It happens to our project when we have Bulk CSV upload which usually take 30-50 seconds, but if someone during this time fires and open other page of website they start to get random Database related errors. From SqlReader is close to field “Xyz” is not available, as they tend to share the connection with other requests.

Hope you avoid those problem. The colleague who did this in our project is not working with us anymore, but hey, where ever you are , please don’t punish us like this again !!!

Categories
SQlite

SQLite Bulk Insert on ADO.NET

Today, I was trying to find an alternative on reading a Binary data file to use information in my software. For this I choose to use SQlite the embedded database. It has quite a few steps before I achieve a acceptable speed with database and here is list of those that need to be taken care for BULK INSERT in SQLite database.

1. Try to simplify your for or while loop as much as you can. For example do not construct the Query within Loop. Give String Query before the start of loop.

2. Loop should be enclosed within the Transaction. Somebody techincally explain me that with Tranasction the fsync function which is responsible of ensuring the  data safely written, is called once only. If you don’t use Transaction fsync is called on every insert query and proves to be a bottleneck in performance.

3. Index your Table if you have to “update” the result within the Loop. In my case I have to first find one value in my Binary file, write it down to Database, then in one of condition to read of file, I might get other value to UPDATE my existing record. So, within my loop I have insert and update commands. And indexing makes it easier to find record when I got more than 50-60K record inserted. It make the speed upgrade of 1500% in my case with just 0.1M records. I have to read 1.5M records.

SQLite is really fun. It works charm with proper techniques used and store more than 1M record for me to work on.

Categories
Database MS SQL Server SQL

Sql optimization

Today, I experience a slow down in one of website I just finish building up. It was a intereting project which has ability to create Dynamic form and hence the related dynamic database tables. But the problem was in part where we preload user information and later show them on our dynamic forms when user code matches from database. During development I was using list of about 30-40K records and it works just fine, but within couple of days of project going live we encounter that our user database is about 800K records and now the search takes as many as 30 seconds to returns the results.

I create few more indexes on concern table as the SQL was operating on joining 3 tables in first table we have 800K records, other tables has about 200 records each. After indexes the time was shorten to 25s, which again is high time for me. So, I thought to rewrite the query in different manner but suddenly I realise that for user code matching we are using 2 fields from main table and I was concatinating those two fields in one and passing combine value to query. I then thought to test it without concatination and magic happens now the same joins take less than a second to fetch records.

So morale of story is Concatination at runtime is not recommended in where clause, specially.

Categories
Database MS SQL Server SQL

Reseed Auto Increment Value in SQL Server

You can reseed the Auto increment field to desire number by using following SQL statement [I think it is specific for SQL Server only] other Database have their own methods.

DBCC CHECKIDENT (<TableName>, RESEED, 0)

Works great for me on Sql Server 2008

Categories
Joyous MySQL Operating System

MySql 5.5 : funny about it

Just saw the release note of MySql 5.5. Looks exciting upgrade, but what looks most funny about the software is the fact that they claim to improve performance by 1500% on windows [some 372% on linux]. Looks great work by them. But I found this information funny, as my some Open source lover friends always claim that because of windows bad structure mysql perform bad on Windows machine as compare to linux. But now my argument to them is if it is bad structure of windows, then how they achieve 1500% improvement by improving their code and not windows ? Often people forgot when comparing software that run on multi platform that the optimization of core software can only be for either OS [if they share same code], and that cause performance issues.

But whatever it is, I am happy to see MySql perform better on windows as I am bound to use windows with kind of resources I can afford for my development and maintainability I can handle.

Good work Team !!!

Categories
MySQL Server Configuration

MySQL : Crash and Upgrade

Today, I face a new problem with MySQL. I got the database to crash quite a few times since morning. Event logs shows that there is a fault in mysqld.exe, but that error was not very clear on specific reason of fault. After searching for articles and forums, I got a hint that in past [about 3-4 yrs], one guy has same random crash problem and he found that crash was related to some store procedure. This give me a clue and I found that in my case it is a corrupted database file. I need to investigate as why that database got corrupted as it is the latest database created in system and was working fine just another day.

Anyways, this lead me to upgrade database server after 6-7 months. It was pretty easy though, just download the new version and run the setup. everything was done automatically. Isn’t it that very simple.