Nik's Technology Blog

Travels through programming, networks, and computers

Data Layer with Business Logic

Deciding when to move business logic to the data layer in a multi-tiered application is a tricky one and depends a lot on the RDMS you are using.
I've been looking at an ASP.NET application that creates 7 rows in a database for each "musical performance" when a button is clicked on a web form to rebuild a database.
To begin with a SQL 2005 stored procedure written in VB.NET (But could easily have been C# with the same results since all .NET languages are compiled to IL). was called from a loop in the business layer 7 times to create the 7 rows. The application was run and it took about 90 seconds to rebuild a test database in this manner.

This logic was then moved into 1 stored procedure still written in VB.NET; The result was that instead of making 7 calls to the database each time, we make 1 call to the database and move the business logic, executing the loop there, and hence at the data layer. The database rebuild took about 30 seconds this time.

Stored Procedures in Native T-SQL

To try and make the execution faster we decided to write the stored procedure in T-SQL, which is still the native language of SQL Server. This shaved another 15 seconds of the execution time! Just proving what sort of overhead writing your stored procedures in anything other than T-SQL introduces.

This exercise provides a glimpse into how the decisions you make when designing an application radically affect the responsiveness of the system.

Business Logic at the Data Layer

If you were pulling lots of data to the business layer performing some processing then discarding most of the data at that point, then it may improve performance if this operation was moved down to the data layer. It really depends on the data and the application.
One of the problems with storing too much application logic in the data layer is version control. It can become a mine field managing lots of complex stored procedures between test and live environments and keeping them in-sync with the application.

MySQL vs TSQL Concatenation CONCAT()

Following on from an entry posted last year concerning converting SQL queries from Microsoft TSQL to MySQL I've come across another function which is treated somewhat differently in MySQL than TSQL.

The following SQL code adds the .jpeg file extension onto the end of the image names from tblImages.

TSQL Concatenation

SELECT ImageName+'.jpeg' FROM tblImages

The above SQL syntax is used on Microsoft SQL Server, Microsoft Access and Sybase

MySQL Concatenation

SELECT CONCAT(ImageName,'.jpeg') FROM tblImages

Oracle Concatenation

SELECT ImageName || '.jpeg' FROM tblImages

The Oracle syntax is the syntax recommended by the SQL specification. It uses 2 pipes || instead of a plus + sign. This avoids the problems of trying to concatenate integer fields which might otherwise result in an unwanted addition.

Previous entry: T-SQL TO MYSQL CONVERSIONS

T-SQL to MySQL Conversions

I've been converting my live web statistics page to PHP and MySQL and after spending a while trying to find MySQL alternatives to Transact SQL (T-SQL) only SQL statements, I thought I'd share a few common solutions to the problems I eventually managed to solve.

Microsoft developers commonly use TOP in a select statement to select the first n rows from a table. The MySQL equivalent is LIMIT, which is superior in that you can specify what row to start on and well as how many to retrieve.

SELECT TOP 20 * FROM MY_TABLE

SELECT * FROM MY_TABLE LIMIT 0,20

Other problems arise from T_SQL's use of Visual Basic (VB). So statements using LCASE, UCASE, INSTR and MID won't work. MySQL of course has alternatives...

SELECT LOWER(name) FROM MY_TABLE

SELECT UPPER(name) FROM MY_TABLE

The SUBSTRING MySQL statement is equivalent to MID or LEFT if you start at the first character. The following statement would result in abcd.

SELECT SUBSTRING('abcdefghijklmnopqrstuvwxyz',1,4)

VB's INSTR is another useful function in T-SQL, the MySQL equivalent is LOCATE. Like INSTR you define a starting point in which you would like to start searching within the string. The following SQL statement would result in 4.

SELECT LOCATE('d','abcdefghijklmnopqrstuvwxyz',1)

MySQL's version of T-SQL's LEN function works in exactly the same manor, only it's called LENGTH. The following statement will result in 26.

SELECT LENGTH('abcdefghijklmnopqrstuvwxyz')

MySQL Manual - String Functions

Importing data from one Database to another with slightly different sized fields?

Preparation is the key to a faultless import.

Sometimes it's useful to know the maximum length of the data you store in your database. Even if you know the maximum size of the cell, the actual data stored may be smaller.

In Microsoft SQL use:

SELECT MAX(LEN(firstname))

FROM tblData

This MS SQL command will find the maximum length of the data in the firstname field of tblData table.

Using this method you can avoid having to increase the destination tables field sizes in cases where you haven't actually used the full capacity in the source database.

So if the source field size for firstname is varchar 50 and the destination is varchar 40, you can use the above SQL to determine if anyone's first name is actually longer than 40 characters. If it's not then you don't need to increase the cell size in the destination table.

If you get the following error when you try to use the LEN() SQL statement above, then you are trying to find the maximum length of a text field.

Error: Argument data type text is invalid for argument 1 of len function.

For text fields in SQL use Datalength() instead of LEN().

SELECT MAX(DATALENGTH(Description))

FROM tblJobs

The Datalength() function which will return the length of any expression, in this case the maximum length of the description field from the tblJobs table.

This can be used on all data types including text, ntext, image and varbinary.

Of course a perfectly designed database would have as little spare capacity in fields as possible, but with fields such as people's names this is hard to achieve this.

Determining which SQL Server Service Pack you have

Before installing Microsoft SQL Service Packs you need to find out which version you are currently running. The commands below should be run in Query Analyser.

SQL 2000

SELECT SERVERPROPERTY('productversion'), SERVERPROPERTY ('productlevel'), SERVERPROPERTY ('edition')

RTM 2000.80.194.0

SQL Server 2000 SP1 2000.80.384.0

SQL Server 2000 SP2 2000.80.534.0

SQL Server 2000 SP3 2000.80.760.0

SQL 7

SELECT @@VERSION

7.00.1063 SQL Server 7.0 Service Pack 4 (SP4)

7.00.961 SQL Server 7.0 Service Pack 3 (SP3)

7.00.842 SQL Server 7.0 Service Pack 2 (SP2)

7.00.699 SQL Server 7.0 Service Pack 1 (SP1)

7.00.623 SQL Server 7.0 RTM (Release To Manufacturing)

SQL 6.5

SELECT @@VERSION

6.50.479 SQL Server 6.5 Service Pack 5a (SP5a) Update

6.50.416 SQL Server 6.5 Service Pack 5a (SP5a)

6.50.415 SQL Server 6.5 Service Pack 5 (SP5)

6.50.281 SQL Server 6.5 Service Pack 4 (SP4)

6.50.258 SQL Server 6.5 Service Pack 3 (SP3)

6.50.240 SQL Server 6.5 Service Pack 2 (SP2)

6.50.213 SQL Server 6.5 Service Pack 1 (SP1)

6.50.201 SQL Server 6.5 RTM

Note: If the version number of your server is not listed here then please visit Microsoft's site for the latest versions. Hotfixes and security patches are not listed.

Fix Orphaned Users in SQL Server after Restoring a Database to another Server

Orphaned users can be created in a database when it is restored to a different server. This happens because not only does the database keep a record of users (sysusers), but so does SQL Server (sysxlogins) in the Master database.

So when you do a restore to a different server the SQL logins in the Master database don't link to the users in the restored database, this is the case even if a login exists with the same name. This is the case because each login has an associated SID (Security Identifier).

Common symptoms of this problem are:

  • Applications may experience 'login failed' error messages and fail to log into the database.
  • Users won't show up in Enterprise Manager, but when you try to add these users, you will get error messages saying 'User or role already exists in the current database'

Solution:

Used stored procedure - sp_change_users_login. You will need to create the account you wish to change users to before running this stored procedure.

--Change the user account to link with the 'NewHarry' login.

USE Books

go

EXEC sp_change_users_login 'Update_One', 'Harry', 'NewHarry'

This will change the user Harry in the restored database to link with NewHarry instead.

How to troubleshoot orphan users in SQL Server databases?

Fix Orphaned Users in SQL Server after Restoring a Database to another Server

Orphaned users can be created in a database when it is restored to a different server. This happens because not only does the database keep a record of users (sysusers), but so does SQL Server (sysxlogins) in the Master database.

So when you do a restore to a different server the SQL logins in the Master database don't link to the users in the restored database, this is the case even if a login exists with the same name. This is the case because each login has an associated SID (Security Identifier).

Common symptoms of this problem are:

  • Applications may experience 'login failed' error messages and fail to log into the database.
  • Users won't show up in Enterprise Manager, but when you try to add these users, you will get error messages saying 'User or role already exists in the current database'

Solution:

Used stored procedure - sp_change_users_login. You will need to create the account you wish to change users to before running this stored procedure.

--Change the user account to link with the 'NewHarry' login.

USE Books

go

EXEC sp_change_users_login 'Update_One', 'Harry', 'NewHarry'

This will change the user Harry in the restored database to link with NewHarry instead.

How to troubleshoot orphan users in SQL Server databases?

Adding Contraints to SQL tables

Constraints are important within a database structure as they insure database integrity.

This useful SQL command shows how to add constraints to existing SQL tables.

In this command a constraint between 2 tables (tblCountries and tblCompanies) is being created called FK_tblCompanies_tblCountries.

alter table dbo.tblCompanies add constraint FK_tblCompanies_tblCountries foreign key (ISOCountryCode) references dbo.tblCountries(ISOCountryCode)