T-SQL In Domestic and International DBs

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

T-SQL In Domestic and International DBs

Author

Topic

rdugre
Starting Member

32 Posts

Posted - 2004-07-15 : 14:24:38

I need some advice, so here is a case scenario for anyone that has some:

I have two separate databases that are identical with the exception that some fields in the first database are varchar and those same fields in the second database are nvarchar. Each database has a TON of data in them. I call the first database a "domestic" installation of my software versus the second which I call "international".

Now I have many stored procedures within the two databases that I need to keep in sync (I use SQL Compare). The issue is that with one database using varchar fields and the other using nvarchar fields, I am now maintaining separate sets of stored procedures (because I am currently using "UPDATE tblSample SET txtField='SAMPLE'..." in the first versus "UPDATE tblSample SET txtField=N'SAMPLE'..." in the second). The "N" being the difference.

My question is: Is there any downside or performance issues using the second UPDATE statement in both databases? Ultimately, I would like to only maintain one set of stored procedures, if possible.

Does anyone have any experience or recommendations for this case scenario?

Thanks in advance.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-07-15 : 14:27:57

You can use the second one when using a varchar column. Why keep two separate databases though? Why can't the international support domestic data too?

Tara

rdugre
Starting Member

32 Posts

Posted - 2004-07-15 : 15:08:47

I am using two different databases because of disk space and performance issues. I have many domestic databases, but only a handful of international databases. Each database is different in size, but many of them are extremely large (its not unusual for them to be much greater than a gigabyte each).

So having all domestic databases take up more space than they need to is undesirable.

Are you aware of any performance issues using the second UPDATE statement? Does it do any unnecessay work that I should be concerned with?

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-07-15 : 15:12:29

Greater than a gigabyte is not large. Large would be greater than 25GB or so. Extremely large would be a terabyte.

I am not aware of any performance issues with using N'String'.

Tara

rdugre
Starting Member

32 Posts

Posted - 2004-07-15 : 15:23:49

I guess that I mean it adds up when you have 100's of these databases. I am trying to minimize all that.

Thanks again.

Kristen
Test

22859 Posts

Posted - 2004-07-15 : 15:39:50

What "international" are you supporting? If its just some "Roman" alphabet languages you might be able to get away with varchar [but you would have to make some decent tests]; varchar will most likely hold foreign accents etc. without problems, but for Chinese etc. you will definitely need nvarchar.

Also, what columns ACTUALLY need to be internationalised? Name and Address, for sure, but there are probably lots of other VARCHAR columns that don't need to be NVARCHAR because they will never hold uni-code characters. That might be a compromise such that you can live with "international" for all users??

Kristen

rdugre
Starting Member

32 Posts

Posted - 2004-07-15 : 16:04:58

Yes, as you mentioned, I do have databases with Japanese and Chinese character sets, so those are the ones I am using nvarchar for. Also, I am only using nvarchar where needed, not everywhere, as I am definitely aware of when to use nvarchar and when not to.

Thanks.

Kristen
Test

22859 Posts

Posted - 2004-07-15 : 16:11:24

Looks like you've got it covered then ...

rdugre
Starting Member

32 Posts

Posted - 2004-07-16 : 10:48:15

For those interested, I decided to perform a little experiment to determine how much of an issue mixing varchar's and nvarchar's really is.

This is by no means a controlled environment test. I did this using ASP.NET code running on Windows XP Professional and IIS 5.0 going up against a localhost installation of SQL Server 2000. My machine is a 1.8Ghz Pentium 4 with 512 MB RAM. The test was run multiple times to ensure somewhat consistent results.

These are my results:

INSERT 1000 varchar's into varchar field = 1.0091 secs
INSERT 1000 nvarchar's into varchar field = 1.0673 secs
INSERT 1000 varchar's into nvarchar field = 0.9999 secs
INSERT 1000 nvarchar's into nvarchar field = 1.0861 secs

UPDATE 1000 varchar's in varchar field = 4.6685 secs
UPDATE 1000 nvarchar's in varchar field = 4.7027 secs
UPDATE 1000 varchar's in nvarchar field = 4.8807 secs
UPDATE 1000 nvarchar's in nvarchar field = 4.6610 secs

SELECT 1000 varchar's by searching varchar field = 1.2284 secs
SELECT 1000 nvarchar's by searching varchar field = 2.1148 secs
SELECT 1000 varchar's by searching nvarchar field = 1.9864 secs
SELECT 1000 nvarchar's by searching nvarchar field = 2.0526 secs

As can be seen in the results, the biggest issue I found was with the second SELECT test versus the first. This confirms what someone else told me on the Microsoft SQL Server Usenet groups.

Hope this helps someone.

Kristen
Test

22859 Posts

Posted - 2004-07-17 : 03:19:20

I'm interested!

> SELECT 1000 nvarchar's by searching varchar field = 2.1148 secs

What's your code for this, are you casting varchar to Nvarchar or somesuch?

Most scary for me (as I don't think we'd move stuff BETWEEN varchar and Nvarchar) is:

SELECT 1000 varchar's by searching varchar field = 1.2284 secs
SELECT 1000 nvarchar's by searching nvarchar field = 2.0526 secs

So basically [uncontrolled test environment etc.] a SELECT on an nvarchar takes twice as long as a varchar

Ouch!

Mind you, doesn't Access default to creating Nvarchar in SQL? That should keep me in Beer and Skittles tuning-up those sort of errors for a few years yet ...

Kristen

derrickleggett
Pointy Haired Yak DBA

4184 Posts

Posted - 2004-07-17 : 09:28:50

What's the longest column you have in VARCHAR format? More specifically, do you have any over 4000 in length?

MeanOldDBA
derrickleggett@hotmail.com

When life gives you a lemon, fire the DBA.

rdugre
Starting Member

32 Posts

Posted - 2004-07-17 : 10:31:48

Here is all the code I used for this test. Please let me know if there is anything wrong, as I did everything pretty quick.

I'll post everything in multiple posts to break it up. Here is the DDL:

CREATE TABLE [dbo].[tblNVarchar] (
	[lngID] [int] IDENTITY (1, 1) NOT NULL ,
	[txtNVarchar] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL 
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[tblVarchar] (
	[lngID] [int] IDENTITY (1, 1) NOT NULL ,
	[txtVarchar] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL 
) ON [PRIMARY]
GO

rdugre
Starting Member

32 Posts

Posted - 2004-07-17 : 10:33:22

Here is the ASP.NET HTML code (VB.NET codebehind is in next post):

<%@ Page Language="vb" AutoEventWireup="false" Codebehind="Default.aspx.vb" Inherits="DatabaseDoubleByteTest.Default1"%>
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<html>
<head>
<title>Database Double Byte Test</title>
<link href="Styles.css" type="text/css" rel="stylesheet">
</head>
<body>
<form id="frmMain" method="post" runat="server">

	<asp:button runat="server" id="cmdRun" text="Run Test"/>
	
	<br><br><br>
	
	<asp:label runat="server" id="lblResults" text=""/>
	
</form>
</body>
</html>

rdugre
Starting Member

32 Posts

Posted - 2004-07-17 : 10:35:19

Finally, here is the VB.NET codebehind:

Public Class Default1
    Inherits System.Web.UI.Page

    Protected WithEvents cmdRun As Button
    Protected lblResults As Label

    Private strConnString As String = "server=(local);database=DatabaseDoubleByteTest;uid=myid;pwd=mypass;"
    Private objStopwatch As New Stopwatch
    Private objDR1 As SqlDataReader
    Private strSQL As String

    Private Sub cmdRun_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles cmdRun.Click
        Dim strHTML As String = ""
        Dim intX As Integer, dblTime As Double

        'Clear out the tables to start fresh.
        strSQL = "DELETE FROM tblVarchar"
        SqlHelper.ExecuteNonQuery(strConnString, CommandType.Text, strSQL)
        strSQL = "DELETE FROM tblNVarchar"
        SqlHelper.ExecuteNonQuery(strConnString, CommandType.Text, strSQL)

        'Insert 1000 varchar's into varchar fields.
        objStopwatch.Start()
        For intX = 1 To 1000
            strSQL = "INSERT INTO tblVarchar (txtVarchar) VALUES ('" & intX.ToString("0000") & _
            "-abcdefghijklmnopqrstuvwxyz0123456789')"
            SqlHelper.ExecuteNonQuery(strConnString, CommandType.Text, strSQL)
        Next intX
        dblTime = objStopwatch.Done
        strHTML &= "INSERT 1000 varchar's into varchar field = " & dblTime.ToString("##0.0000") & " secs<br>"

        'Insert 1000 nvarchar's into varchar fields.
        objStopwatch.Start()
        For intX = 1 To 1000
            strSQL = "INSERT INTO tblVarchar (txtVarchar) VALUES (N'" & intX.ToString("0000") & _
            "-abcdefghijklmnopqrstuvwxyz0123456789')"
            SqlHelper.ExecuteNonQuery(strConnString, CommandType.Text, strSQL)
        Next intX
        dblTime = objStopwatch.Done
        strHTML &= "INSERT 1000 nvarchar's into varchar field = " & dblTime.ToString("##0.0000") & " secs<br>"

        'Insert 1000 varchar's into nvarchar fields.
        objStopwatch.Start()
        For intX = 1 To 1000
            strSQL = "INSERT INTO tblNVarchar (txtNVarchar) VALUES ('" & intX.ToString("0000") & _
            "-abcdefghijklmnopqrstuvwxyz0123456789')"
            SqlHelper.ExecuteNonQuery(strConnString, CommandType.Text, strSQL)
        Next intX
        dblTime = objStopwatch.Done
        strHTML &= "INSERT 1000 varchar's into nvarchar field = " & dblTime.ToString("##0.0000") & " secs<br>"

        'Insert 1000 nvarchar's into nvarchar fields.
        objStopwatch.Start()
        For intX = 1 To 1000
            strSQL = "INSERT INTO tblNVarchar (txtNVarchar) VALUES (N'" & intX.ToString("0000") & _
            "-abcdefghijklmnopqrstuvwxyz0123456789')"
            SqlHelper.ExecuteNonQuery(strConnString, CommandType.Text, strSQL)
        Next intX
        dblTime = objStopwatch.Done
        strHTML &= "INSERT 1000 nvarchar's into nvarchar field = " & dblTime.ToString("##0.0000") & " secs<br>"

        strHTML &= "<br>"

        'Update 1000 varchar's into varchar fields.
        objStopwatch.Start()
        For intX = 1 To 1000
            strSQL = "UPDATE tblVarchar SET txtVarchar='UPDATED-" & intX.ToString("0000") & _
            "-abcdefghijklmnopqrstuvwxyz0123456789' WHERE (txtVarchar='" & intX.ToString("0000") & _
            "-abcdefghijklmnopqrstuvwxyz0123456789')"
            SqlHelper.ExecuteNonQuery(strConnString, CommandType.Text, strSQL)
        Next intX
        dblTime = objStopwatch.Done
        strHTML &= "UPDATE 1000 varchar's in varchar field = " & dblTime.ToString("##0.0000") & " secs<br>"

        'Update 1000 nvarchar's into varchar fields.
        objStopwatch.Start()
        For intX = 1 To 1000
            strSQL = "UPDATE tblVarchar SET txtVarchar=N'UPDATED-" & intX.ToString("0000") & _
            "-abcdefghijklmnopqrstuvwxyz0123456789' WHERE (txtVarchar=N'" & intX.ToString("0000") & _
            "-abcdefghijklmnopqrstuvwxyz0123456789')"
            SqlHelper.ExecuteNonQuery(strConnString, CommandType.Text, strSQL)
        Next intX
        dblTime = objStopwatch.Done
        strHTML &= "UPDATE 1000 nvarchar's in varchar field = " & dblTime.ToString("##0.0000") & " secs<br>"

        'Update 1000 varchar's into nvarchar fields.
        objStopwatch.Start()
        For intX = 1 To 1000
            strSQL = "UPDATE tblNVarchar SET txtNVarchar='UPDATED-" & intX.ToString("0000") & _
            "-abcdefghijklmnopqrstuvwxyz0123456789' WHERE (txtNVarchar='" & intX.ToString("0000") & _
            "-abcdefghijklmnopqrstuvwxyz0123456789')"
            SqlHelper.ExecuteNonQuery(strConnString, CommandType.Text, strSQL)
        Next intX
        dblTime = objStopwatch.Done
        strHTML &= "UPDATE 1000 varchar's in nvarchar field = " & dblTime.ToString("##0.0000") & " secs<br>"

        'Update 1000 nvarchar's into nvarchar fields.
        objStopwatch.Start()
        For intX = 1 To 1000
            strSQL = "UPDATE tblNVarchar SET txtNVarchar=N'UPDATED-" & intX.ToString("0000") & _
            "-abcdefghijklmnopqrstuvwxyz0123456789' WHERE (txtNVarchar=N'" & intX.ToString("0000") & _
            "-abcdefghijklmnopqrstuvwxyz0123456789')"
            SqlHelper.ExecuteNonQuery(strConnString, CommandType.Text, strSQL)
        Next intX
        dblTime = objStopwatch.Done
        strHTML &= "UPDATE 1000 nvarchar's in nvarchar field = " & dblTime.ToString("##0.0000") & " secs<br>"

        strHTML &= "<br>"

        'Select 1000 varchar's by searching varchar fields.
        objStopwatch.Start()
        For intX = 1 To 1000
            strSQL = "SELECT txtVarchar FROM tblVarchar WHERE (txtVarchar='UPDATED-" & intX.ToString("0000") & _
            "-abcdefghijklmnopqrstuvwxyz0123456789')"
            SqlHelper.ExecuteScalar(strConnString, CommandType.Text, strSQL)
        Next intX
        dblTime = objStopwatch.Done
        strHTML &= "SELECT 1000 varchar's by searching varchar field = " & dblTime.ToString("##0.0000") & " secs<br>"

        'Select 1000 nvarchar's by searching varchar fields.
        objStopwatch.Start()
        For intX = 1 To 1000
            strSQL = "SELECT txtVarchar FROM tblVarchar WHERE (txtVarchar=N'UPDATED-" & intX.ToString("0000") & _
            "-abcdefghijklmnopqrstuvwxyz0123456789')"
            SqlHelper.ExecuteScalar(strConnString, CommandType.Text, strSQL)
        Next intX
        dblTime = objStopwatch.Done
        strHTML &= "SELECT 1000 nvarchar's by searching varchar field = " & dblTime.ToString("##0.0000") & " secs<br>"

        'Select 1000 varchar's by searching nvarchar fields.
        objStopwatch.Start()
        For intX = 1 To 1000
            strSQL = "SELECT txtNVarchar FROM tblNVarchar WHERE (txtNVarchar='UPDATED-" & intX.ToString("0000") & _
            "-abcdefghijklmnopqrstuvwxyz0123456789')"
            SqlHelper.ExecuteScalar(strConnString, CommandType.Text, strSQL)
        Next intX
        dblTime = objStopwatch.Done
        strHTML &= "SELECT 1000 varchar's by searching nvarchar field = " & dblTime.ToString("##0.0000") & " secs<br>"

        'Select 1000 nvarchar's by searching nvarchar fields.
        objStopwatch.Start()
        For intX = 1 To 1000
            strSQL = "SELECT txtNVarchar FROM tblNVarchar WHERE (txtNVarchar=N'UPDATED-" & intX.ToString("0000") & _
            "-abcdefghijklmnopqrstuvwxyz0123456789')"
            SqlHelper.ExecuteScalar(strConnString, CommandType.Text, strSQL)
        Next intX
        dblTime = objStopwatch.Done
        strHTML &= "SELECT 1000 nvarchar's by searching nvarchar field = " & dblTime.ToString("##0.0000") & " secs<br>"

        objDR1 = Nothing

        lblResults.Text = strHTML
    End Sub

End Class

Kristen
Test

22859 Posts

Posted - 2004-07-17 : 12:21:40

Can't see much wrong with that ... and I see how the "SELECT 1000 nvarchar's by searching varchar field" works - seems emminnently reasonable

Kristen

AndrewMurphy
Master Smack Fu Yak Hacker

2916 Posts

Posted - 2004-07-19 : 05:04:28

Your timings may be 'tainted'...because of CACHING.

To be 100% accurate you need to clear the cache between the individual components of the 'timed' runs....look into implementing the DBCC DROPCLEANBUFFERS and DBCC FREEPROCCACHE commands.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources