help on performance tuning

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

i thing

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



".nLL" <noone@here.com> wrote in message
news:c3QMj.149467$833.115837@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
>

".nLL" <noone@here.com> wrote in message
news:c3QMj.149467$833.115837@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

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

"Bob Milutinovic" <cognicom@gmail.com> wrote in message
news:O5ifHDrnIHA.1212@TK2MSFTNGP05.phx.gbl...
> ".nLL" <noone@here.com> wrote in message
> news:c3QMj.149467$833.115837@newsfe17.ams2... 
>
> <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

"Bob Milutinovic" <cognicom@gmail.com> wrote in message
news:e1P7qGrnIHA.4536@TK2MSFTNGP06.phx.gbl...
> Oops, force o' habit got in the way here; amended version follows at
bottom.
>
> "Bob Milutinovic" <cognicom@gmail.com> wrote in message
> news:O5ifHDrnIHA.1212@TK2MSFTNGP05.phx.gbl... 
elegant 
>
> 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

thank you very much


"Bob Milutinovic" <cognicom@gmail.com> wrote in message
news:e1P7qGrnIHA.4536@TK2MSFTNGP06.phx.gbl...
> Oops, force o' habit got in the way here; amended version follows at
> bottom.
>
> "Bob Milutinovic" <cognicom@gmail.com> wrote in message
> news:O5ifHDrnIHA.1212@TK2MSFTNGP05.phx.gbl... 
>
> 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
>
>