help on performance tuning

[.nLL]

got a multi languagel site.
my aproach was to create

CREATE TABLE [dbo].[languages](
[lang] [nvarchar](3) NOT NULL,
[lang1] [nvarchar](200) NULL,
[lang2] [nvarchar](200) NULL,
[lang3] [nvarchar](200) NULL,
[lang4] [nvarchar](200) NULL,
........................
<SNIP>
........................
[lang310] [nvarchar](200) NULL
) ON [PRIMARY]

have first row language identifier ( [lang] [nvarchar](3) NOT NULL,) and
rest as language source so lang2 to lang310 would have translations
if i want to add another lang i would just add another row with that
language

and my calssic asp function is

getLang("lang1","en")

-----------------------------------------------------------------------------------------------

Function getLang(myInput,myLang)
Set MyCacheForLanguages = New DataCache
MyCacheForLanguages.ConnectionString = SqlConn
LanguagesSQL = "SELECT * FROM languages where lang='"&myLang&"'"
Set CacheRsForLanguages = MyCacheForLanguages.GetRecordset(LanguagesSQL)
Do While not CacheRsForLanguages.EOF
For i = 0 To CacheRsForLanguages.Fields.Count - 1
If CacheRsForLanguages.Fields.Item(i).name=myInput Then
getLang = CacheRsForLanguages.Fields.Item(i).value
Exit for
End if
Next
CacheRsForLanguages.MoveNext
Loop
Set CacheRsForLanguages = Nothing
Set MyCacheForLanguages = Nothing
End function
note New DataCache , it is memory caching class i use
----------------------------------------------------------------------------------------------
this combination works fine when i have couple of coulmns in language table
but i have over 300 columns and looping slowes server down.

i need another approach to make this combination faster. may be in function
or in select statement.

can anyone think of something faster without changing table stracture

[.nLL]

i thing

altering function as
getLang = CacheRsForLanguages.Fields.Item(myInput).value
and removing loop would do

".nLL" <no***@here.comwrote in message
news:c3*********************@newsfe17.ams2...

got a multi languagel site.
my aproach was to create

CREATE TABLE [dbo].[languages](
[lang] [nvarchar](3) NOT NULL,
[lang1] [nvarchar](200) NULL,
[lang2] [nvarchar](200) NULL,
[lang3] [nvarchar](200) NULL,
[lang4] [nvarchar](200) NULL,
........................
<SNIP>
........................
[lang310] [nvarchar](200) NULL
) ON [PRIMARY]

have first row language identifier ( [lang] [nvarchar](3) NOT NULL,) and
rest as language source so lang2 to lang310 would have translations
if i want to add another lang i would just add another row with that
language

and my calssic asp function is

getLang("lang1","en")

-----------------------------------------------------------------------------------------------

Function getLang(myInput,myLang)
Set MyCacheForLanguages = New DataCache
MyCacheForLanguages.ConnectionString = SqlConn
LanguagesSQL = "SELECT * FROM languages where lang='"&myLang&"'"
Set CacheRsForLanguages = MyCacheForLanguages.GetRecordset(LanguagesSQL)
Do While not CacheRsForLanguages.EOF
For i = 0 To CacheRsForLanguages.Fields.Count - 1
If CacheRsForLanguages.Fields.Item(i).name=myInput Then
getLang = CacheRsForLanguages.Fields.Item(i).value
Exit for
End if
Next
CacheRsForLanguages.MoveNext
Loop
Set CacheRsForLanguages = Nothing
Set MyCacheForLanguages = Nothing
End function
note New DataCache , it is memory caching class i use
----------------------------------------------------------------------------------------------
this combination works fine when i have couple of coulmns in language
table
but i have over 300 columns and looping slowes server down.

i need another approach to make this combination faster. may be in
function
or in select statement.

can anyone think of something faster without changing table stracture

[Bob Milutinovic]

".nLL" <no***@here.comwrote in message
news:c3*********************@newsfe17.ams2...

got a multi languagel site.
my aproach was to create

CREATE TABLE [dbo].[languages](
[lang] [nvarchar](3) NOT NULL,
[lang1] [nvarchar](200) NULL,
[lang2] [nvarchar](200) NULL,
[lang3] [nvarchar](200) NULL,
[lang4] [nvarchar](200) NULL,
........................
<SNIP>
........................
[lang310] [nvarchar](200) NULL
) ON [PRIMARY]

<snip>

How does one express profuse head-shaking using only 7-bit ASCII? Aaaaaargh!

Try applying some logic to the problem, and you'll find a far more elegant
'n' functional solution, infinitely easier to manage, and massively more
scalable.
CREATE TABLE Languages (
ID int IDENTITY(1,1) NOT NULL,
Lang nvarchar(5) NOT NULL,
Phrase nvarchar(200) NOT NULL)

......

FUNCTION getLang(iPhrase, sLang)
'--- assumes cDB is an already-established database connection
'--- input value sanitisation should be implemented!
sFunctionResult = ""
SET rsResult = cDB.Execute("SELECT Phrase FROM Languages WHERE ID=" &
iPhrase & " AND Lang='" & sLang & "'")
IF NOT rsResult.EOF THEN
sFunctionResult = rsResult("Phrase")
END IF
SET rsResult = NOTHING
getLang = sFunctionResult
END FUNCTION

[Bob Milutinovic]

Oops, force o' habit got in the way here; amended version follows at bottom.

"Bob Milutinovic" <co******@gmail.comwrote in message
news:O5**************@TK2MSFTNGP05.phx.gbl...

".nLL" <no***@here.comwrote in message
news:c3*********************@newsfe17.ams2...

>got a multi languagel site.
my aproach was to create

CREATE TABLE [dbo].[languages](
[lang] [nvarchar](3) NOT NULL,
[lang1] [nvarchar](200) NULL,
[lang2] [nvarchar](200) NULL,
[lang3] [nvarchar](200) NULL,
[lang4] [nvarchar](200) NULL,
........................
<SNIP>
........................
[lang310] [nvarchar](200) NULL
) ON [PRIMARY]

<snip>

How does one express profuse head-shaking using only 7-bit ASCII?
Aaaaaargh!

Try applying some logic to the problem, and you'll find a far more elegant
'n' functional solution, infinitely easier to manage, and massively more
scalable.

CREATE TABLE Languages (
ID int IDENTITY(1,1) NOT NULL,
PhraseID int NOT NULL,
Lang nvarchar(5) NOT NULL,
Phrase nvarchar(200) NOT NULL)

......

FUNCTION getLang(iPhrase, sLang)
'--- assumes cDB is an already-established database connection
'--- input value sanitisation should be implemented!
sFunctionResult = ""
SET rsResult = cDB.Execute("SELECT Phrase FROM Languages WHERE PhraseID="
& iPhrase & " AND Lang='" & sLang & "'")
IF NOT rsResult.EOF THEN
sFunctionResult = rsResult("Phrase")
END IF
SET rsResult = NOTHING
getLang = sFunctionResult
END FUNCTION

[Anthony Jones]

"Bob Milutinovic" <co******@gmail.comwrote in message
news:e1**************@TK2MSFTNGP06.phx.gbl...

Oops, force o' habit got in the way here; amended version follows at

bottom.

>
"Bob Milutinovic" <co******@gmail.comwrote in message
news:O5**************@TK2MSFTNGP05.phx.gbl...

".nLL" <no***@here.comwrote in message
news:c3*********************@newsfe17.ams2...

got a multi languagel site.
my aproach was to create

CREATE TABLE [dbo].[languages](
[lang] [nvarchar](3) NOT NULL,
[lang1] [nvarchar](200) NULL,
[lang2] [nvarchar](200) NULL,
[lang3] [nvarchar](200) NULL,
[lang4] [nvarchar](200) NULL,
........................
<SNIP>
........................
[lang310] [nvarchar](200) NULL
) ON [PRIMARY]

<snip>

How does one express profuse head-shaking using only 7-bit ASCII?
Aaaaaargh!

Try applying some logic to the problem, and you'll find a far more

elegant

'n' functional solution, infinitely easier to manage, and massively more
scalable.

CREATE TABLE Languages (
ID int IDENTITY(1,1) NOT NULL,
PhraseID int NOT NULL,
Lang nvarchar(5) NOT NULL,
Phrase nvarchar(200) NOT NULL)

You can also ditch the ID column and place the PK across the PhraseID and
Lang columns. ;)

--
Anthony Jones - MVP ASP/ASP.NET

[.nLL]

thank you very much
"Bob Milutinovic" <co******@gmail.comwrote in message
news:e1**************@TK2MSFTNGP06.phx.gbl...

Oops, force o' habit got in the way here; amended version follows at
bottom.

"Bob Milutinovic" <co******@gmail.comwrote in message
news:O5**************@TK2MSFTNGP05.phx.gbl...

>".nLL" <no***@here.comwrote in message
news:c3*********************@newsfe17.ams2...

>>got a multi languagel site.
my aproach was to create

CREATE TABLE [dbo].[languages](
[lang] [nvarchar](3) NOT NULL,
[lang1] [nvarchar](200) NULL,
[lang2] [nvarchar](200) NULL,
[lang3] [nvarchar](200) NULL,
[lang4] [nvarchar](200) NULL,
........................
<SNIP>
........................
[lang310] [nvarchar](200) NULL
) ON [PRIMARY]

<snip>

How does one express profuse head-shaking using only 7-bit ASCII?
Aaaaaargh!

Try applying some logic to the problem, and you'll find a far more
elegant 'n' functional solution, infinitely easier to manage, and
massively more scalable.

CREATE TABLE Languages (
ID int IDENTITY(1,1) NOT NULL,
PhraseID int NOT NULL,
Lang nvarchar(5) NOT NULL,
Phrase nvarchar(200) NOT NULL)

.....

FUNCTION getLang(iPhrase, sLang)
'--- assumes cDB is an already-established database connection
'--- input value sanitisation should be implemented!
sFunctionResult = ""
SET rsResult = cDB.Execute("SELECT Phrase FROM Languages WHERE
PhraseID=" & iPhrase & " AND Lang='" & sLang & "'")
IF NOT rsResult.EOF THEN
sFunctionResult = rsResult("Phrase")
END IF
SET rsResult = NOTHING
getLang = sFunctionResult
END FUNCTION