Performance - String Vs Number?
Apr 11, 2008
Hi guys,
I have 2 tables:
Unit( unit_id integer, unit_name varchar(20) )
Part( part_id integer, part_name varchar(20) )
Then I have a join table between the two
Unit_Part( unit_id, part_id )
Each unit NAME and part NAME is unique. So what kind of performance would I be losing if I were to do something like:
PROCEDURE addPartToUnit( VARCHAR(20) sPartName, VARCHAR(20) sUnitName )
-- Now in this procedure i'll just get the id's via the names from a select statement
Versus
PROCEDURE addPartToUnit( nPartId INTEGER, nUnitId INTEGER )
-- In this proc, i directly input the primary keys of the table so no lookup is necessary
The reason is that I would like for developers to be able to write code without having to know the id's.
What do you think?
View 14 Replies
ADVERTISEMENT
Jul 14, 2015
I have a text field which has entries of variable length of the form:
"house:app.apx&resultid=1234,clientip"
or
"tost:app.apx&resultid=123,clientip"
or
"airplane:app.apx&resultid=123489,clientip"
I'm trying to pick out the numbers between resultid='...',clientip no matter what the rest of the string looks like. So in this example it would be the numbers:
1234
123
12389
the part of the string of the form resultid='...',clientip always stays the same except the length of the number can vary.
View 5 Replies
View Related
Apr 25, 2007
Hi,I'm designing a new database and I have a doubt in which surely youcan help me.I'm storing in this database historical data of some measurements andthe system in constantly growing, new measurements are added everyday.So, I have to set some extra columns in advance, so space is availablewhenever is needed and the client doesn't have to modify the structurein SQL server.The question is: the more columns I add "just in case", the slower theSQL reads the table?Of course the "empty" columns are not included in any query until theyhave some valid data inside.Will I have better performance if I configure only the columns beingused at the moment, without any empty columns?Thanks in advance.Ignacio
View 2 Replies
View Related
Dec 28, 2007
Hi,
I have a denormalized table (done so with reason) with around 40 columns. I would never have to retrieve data for all of those columns together.
I haven't done any performance measurements yet but just wondering if anyone has ready answer to this: Will there be a performance degradation if I retrieve data from a table with many columns, even if not all columns are referred in the query? (for making it simple, lets assume that all or varchar type of columns, I just want to find out if performance degrades if there are too many columns in table)
Thanks in advance,
Sandeep
View 1 Replies
View Related
Jan 25, 2008
Hi gurus, I'm creating a web application where I will have a large number of tables (between 10k and 20k), this is done for the sake of scalability as tables will be moved to different database servers as the application grows and also for performance (smaller indexes). I'm worried though how having a large number of tables could affect the performance of SQL Server as the application will start on one single database server. I tried to find some resources on that on the internet but couldn't find any.
I would really appreciate if you can give me some advice and if you have any good links that would be great...
View 10 Replies
View Related
Jan 25, 2008
Hi gurus, I'm creating a web application where I will have a large number of tables (between 10k and 20k), this is done for the sake of scalability as tables will be moved to different database servers as the application grows and also for performance (smaller indexes). I'm worried though how having a large number of tables could affect the performance of SQL Server as the application will start on one single database server. I tried to find some resources on that on the internet but couldn't find any.
I would really appreciate if you can give me some advice and if you have any good links that would be great...
Waleed Eissa
http://www.waleedeissa.com
View 9 Replies
View Related
Aug 26, 2005
I have a string in form "abcdefg 12355 ijklmn"Now I want get only the number 12355 within the string !!Is there any function available in T-SQL of Sql server 2K??Thanksfor any help
View 6 Replies
View Related
Oct 15, 2001
I've just upgraded from Access to SQL 2000 and am having problems with a line of SQL code.
In Access I sorted on a text column of part numbers where a number within the text was the important sort value.
Text was typically "AB 1234 A" "AB 34 B" "AB 73000"
The important sort has to be on the numeric value of the part number.
Access Code that worked was:
ORDER BY Val(MID(Parts.PartNo,4,6))
Seeing both Val() and MID() are not acceptable in SQL 2000 I ended up using
Substr(Parts.PartNo,4,6) but this returns a string sort where 100 appears before 25 etc.
Can anyone help point me in the right direction?
Many thanks
Peter
View 1 Replies
View Related
Jan 13, 2005
Quick question I hope:
Is there a way to convert a string into a unique number. I have tried using checksum(str) but this appears not to be unique.
For example:
select checksum('KEY AND KEY')
select checksum('COPENBARGER AND COPENBARGER')
both yield -2027749374.
Can someone suggest an alternative function?
Many thanks :)!
View 14 Replies
View Related
Dec 14, 2007
Hi
ID=2763&Department=SLA
How do I get number between 'ID=' and '&' from the above string. The digits vary from 2 to 6 digits
Advance Thanks
View 20 Replies
View Related
Aug 3, 2005
Hi,Does anyone have any suggestions on an approach to speed this upplease?I need to check a given column in a given table to see whether thereare any characters within any of the records that fall outside of arange of valid ASCII characters.My problem is that of speed. The approach I have taken is to write afunction that iterates the string character by character and checks theASCII value at the current position. If it is deemed invalid I drop outof the function and return True. The calling procedure then knows tostop searching as the table column has already failed.Running down a table of million rows, can take up to an hour dependingon the datatype and level of data population.If I write the same thing in Access 2003 VBA and run it via ODBC ittakes a matter of minutes.I would like to find a set-based way of doing this maybe PATINDEX orsomething else creative but am stumped.Does anyone have any brilliant ideas?Thank you!
View 5 Replies
View Related
Oct 1, 2014
Is there a performance limit on the number of indexes per table / database ? With Filtered indexes there appear to be many more opportunities for more finely defined, and therefore smaller indexes resulting in many more indexes on a single table.
View 4 Replies
View Related
Jul 7, 2006
I search the help for T-sql, still don't know how to convert a number, for example 12345 to a string with formating like 12,345
Thanks for your help.
View 1 Replies
View Related
Dec 6, 2014
I have a column which keeps numeric data in form of String, but I want to convert it to number e.g. from "1001" to 1001,
Which function I can use to do it?
[URL] .....
View 2 Replies
View Related
Dec 14, 2007
I have table (log)as below
ID---Qstring----------------------------------------------ProID
1----ID=25375
2----ID=23382
3----lngSessionId=134513044&pid=25375
52---lngSessionId=133466044&pid=27890
67---tduid=dc79b&url=http://www.mydomain.co.uk/proddetails.asp?id=186
How do I read only the number starts with ID and pid from the above string and insert into column 'ProID' of the same row? It is log table tracking all the visiters to the web site. The number next to IDs and pids are productIDs. So if I manage to get product ID to the column ProId then I can hit against other table to find out which product the customer clicked.
I planned to do the above said by trigger. Please help
View 7 Replies
View Related
Jun 15, 2006
how to convert number to string in sqlservereg:- 10 -> ten display*** Sent via Developersdex http://www.developersdex.com ***
View 1 Replies
View Related
Jul 20, 2005
Hi,I want to get the string representation of a hex number from avarBinary column of a table.For example I want to get the output : 'The Hex value is 0xFF'butselect 'The Hex value is ' + convert(varchar(10), 0xFF)retruns the ascii charecter for 0xFFAny idea how do I get the hex form as it is?thanks.Supratim
View 2 Replies
View Related
May 9, 2008
Hi,
ORDER BY will sort string (varchar) field which "11" < "2", is there any way I can sort them as number so "11" > "2"?
what if there is character inside, like "11a", "2b", in this case, it should sort like, "1" "2" "11" "1a" "2a" "11a" "1b" ....
Is this possible?
also, I tried to google this but not find anything help. what's best keyword to use?
Thank you,
Wes
View 4 Replies
View Related
Apr 16, 2008
I am trying to convert some strings to number datatype. Some numbers are like this.
1,45.45-
21.21-
4,611.20-
Is it possible to convert these type of strings to number datatype.
Thank you.
View 6 Replies
View Related
Jul 19, 2006
hi,
I have a bit string like '000111010101101110000111' , how to convert it to varbinary format like 0x1D5B83?
Thanks.
View 1 Replies
View Related
Dec 12, 2007
Hi
We are using the SQL Server 2005 Full Text Service. The data is not huge, but the kind of data is that each record is small and there are a large number of records. There are 35 million records now with 11 GB of data and about 1.6 GB of FT catalog on the table. This is expected to grow to at least 10 times the size of this data. The issue is with FTS taking a long time to return results when the number of hits (rows) getting returned from FTS is large for some searches, it takes a very long time. With the same data & catalog, those full text queries for less common words return timely. The nature of the problem doesnt allow us to only have top results. We need all the results. So it’s not about the size of data but the number of results getting returned from FT. (As the catalog is inverted). The machine is dual processor with 4 GB RAM.
I am considering splitting the table and hence the catalog and using multiple servers to do full text searches in smaller catalogs. Is there any other way this issue can be solved ?
If splitting is the only way, can you give me an idea as to what is a statistical/standard limit to the number of search results/cataog size as which FTS gives good results
Thanks in advance
View 1 Replies
View Related
Sep 15, 2014
I have one question what is performance difference between cluster index on numeric field or string field? I know that numeric is faster but why it is faster?
View 1 Replies
View Related
Apr 12, 2007
Dear Friends,
I have a doubt relation when converting a field€¦
I €˜m converting a string to datetime inside a SQL command in an OLE DB Source using this:
convert(datetime,[Maturity],103) As MaturityDate
It€™s better to use inside the OLE DB Source or is better to convert it with data conversion transformation??
Regards!!
Thanks
View 11 Replies
View Related
Jan 8, 2007
Lets say I have a table named Projects with the data below.Y07001Y07002Y07003Y07010Y07011I want to pick up the last 3 numbers and add a one to it so.
Declare @Count as varchar(3)
SELECT Top 1 @Count = Right(Project, 3)+1 FROM Projects Order by ID desc
SELECT @Count
This is what I am using. It picks up the last entry, add a one to it and gives me 12 in return. What i want is 012, not 12
View 1 Replies
View Related
Apr 24, 2008
Hi,
In short:
I want my following problem to work with a LIKE instead of exact match and if possible be faster. (currently 4s)
Problem:
I got a set of rows with varchar(50), spread out over multiple tables.
All those tables relate to a central Colour table.
For each of the columns, I want to match the values with a set of strings I insert and then return a set of Colour.Id
E.g: input: 'BLACK', 'MERCEDES', '1984'
Would return colour ids "025864", 45987632", "65489" and "63249"
Because they have a colour name containing 'BLACK' or are on a car from 'MERCEDES' or are used in '1984'.
Current Situation:
I) Create a table containing all possible values
CREATE TABLE dbo.CommonSearch(
id int IDENTITY (1, 1) NOT NULL,
clr_id int,
keyWord varchar(40),
fieldType varchar(25)
And fill it with all the values (671694 rows)
)
II) Stored Procedure to cut a string up into a table:
CREATE FUNCTION dbo.SplitString
(
@param varchar(50),
@splitChar char = ''
)
RETURNS
@T TABLE (keyWord varchar(50))
AS
BEGIN
WHILE LEN( @param ) > 0 BEGIN
declare @val varchar(50)
IF CHARINDEX( @splitChar, @param ) > 0
SELECT @val = LEFT( @param, CHARINDEX( @splitChar, @param ) - 1 ) ,
@param = RIGHT( @param, LEN( @param ) - CHARINDEX( @splitChar, @param ) )
ELSE
SELECT @val = @param, @param = SPACE(0)
INSERT INTO @T values (@val)
END
RETURN
END
III)Stored Procedure to query the first table with the second one
CREATE PROCEDURE [dbo].[GetCommonSearchResultForTabDelimitedStrings]
@keyWords varchar(255) = ''
AS
BEGIN
SET NOCOUNT ON;
select clr_id, keyWord, fieldType
from dbo.commonSearch
where keyWord in (select * from splitString(@keyWords, ''))
END
So, how can I use a LIKE statement in the IN statement of the last query.
Furthermore, I was wondering if this is the best sollution to go for.
Are there any better methods? Got any tuning tips to squeeze out an extra second?
View 5 Replies
View Related
Jul 20, 2005
I've got a string of numbers, say 123456 (the actually number is 12 digitslong). I need to calculate the sum of each individual number in the stringof numbers, so in the example of 123456 the sum would be 21 (1+2+3+4+5+6).Thanks
View 5 Replies
View Related
Oct 29, 2007
Hello
I am importing from a comma delimited text file. A certain string column has values that represent numbers, such as "US$ 20", "30", "40 - 50". I would like to import the number, and if the cell does not contain a string representing a number (example: "I don't know"), a NULL value should be imported instead for that cell.
Thanks a lot.
View 1 Replies
View Related
Apr 17, 2008
Hello experts,
Suddenly I find myself in need of a SQL search that ONLY starts at a record. I need to page through the existing records, but start at the users choice.
View 8 Replies
View Related
Jul 11, 2001
Hi,
I was wondering what would be the best way to remove special characters like, '-', '&' '(',')','#','*', etc... from a number string. To be specific a phone Number string where the string is >= 10.
Thanks, Mark
View 1 Replies
View Related
Aug 8, 2001
Hi all,
@str varchar(500)
select @str = 'abcd,efgh,i,jklmn,opsqrtuv'
For the above string, which string function can be used to find the number of occurences of a particular character, for example, ','? For this example, the answer should be 4. Is there any built-in function in SQL to do this?
Thanks in advance,
-Praveena
View 1 Replies
View Related
May 2, 2014
In t-sql 2008 r2, I would like to know how to select a specific string in a varchar(50) field. The field in question is called 'CalendarId'.
This field can contain values like:
xxIN187 13-14 W Elem
HS321 13-14 D Elem
IN636 13-14 C Elem
030 13-14 clark middle.
What I am looking for is the first position that contains a number value for the length of 3. Thus what I want are values that look like the following: 030, 636, 187.What I know that I want is substring(CalendarId,?,3).The question mark is where I want the starting location of a number value (0 to 9) of the value in CalendarId . I tried pathindex but my syntax did not work.
View 6 Replies
View Related
Apr 16, 2015
I have a query which runs exactly as I want it to. Let's say it returns this in a table:
IDNumber
123
212
312
I want to change (find and replace?) all the number 12s in the 'Number' column for a string. I want to change the 12s to the word: HelloWorld. I don't want to update the table, merely the table that is being returned in the query.
View 1 Replies
View Related
Sep 14, 2007
I would like to convert a number into fixed format string. Say for example if I have number 5, I would like to show it as four charactered string:
0005.
In case if I have 33, then I would like to have a result like '0033'. Please let me know the string functions with which I can acheive this.
View 5 Replies
View Related
