Formatting T-SQL Performacne Boost?????
May 5, 2006
Hey, I'm using PHP with MSSQL, and I'm not having any performace problems or anything, but at the same time I'm trying to optimise our system to work as best as it can as it's going to have a very heavy load once we launch.
I'm running into the age old problem with the battle between optimizing your code and still keeping it readable. I read somewhere that using whitespace in SQL queries is really bad as it take a lot more bandwidth and puts more stress on SQL Server parsing the SQL it is sent. Right now I have a query like so:-
<?php
$selQ = 'SELECT
n.pknewsID AS newsID,
n.title,
n.full_text,
n.publish_up AS datePublished,
CONCAT(u.fname," ",u.lname) AS author,
i.loc AS img_file,
i.descr AS img_caption
FROM tblnews n
LEFT JOIN tblusers u
ON n.fkcreated_by = u.pkuserID
LEFT JOIN tblnews_images i
ON i.fknewsID = n.pknewsID
WHERE
n.pknewsID = '.$articleID.' AND
n.published = 1
LIMIT 1';
$selQ = $DB->setQuery($selQ);
/**
* $DB->setQuery basically is a preg_replace function that
* removes all the tabs in the query string, and replaces them
* with a single space.
*
*/
echo $selQ;
/**
* Printed out it looks like this :-
* SELECT
* n.pknewsID AS newsID
* n.title
* n.full_text
* n.publish_up AS datePublished
* CONCAT(u.fname," ",u.lname AS author,
* i.loc AS img_file,
* i.descr AS img_caption,
* FROM tblnews n
* LEFT JOIN tblusers u
* ON n.fkcreated_by = u.pkuserID
* LEFT JOIN tblnews_images i
* ON i.fknewsID = n.pknewsID
* WHERE
* n.pknewsID = 6 AND
* n.published = 1
* LIMIT 1
*
*/
?>
So the question I have is, does it really matter how the SQL query is sent? I mean, if I put it all on one line (which would kinda suck as it is harder for me to read then), would it speed up transactions significally?
View 1 Replies
ADVERTISEMENT
Mar 16, 2007
Records are added to a table which can grow in millions with date of the creation. Views (also cascade of views) is used to calculate the grouped data:
for example:
1) here is a view to calculate aggregate data:
CREATE VIEW dbo.All_Campaign_Calls AS
SELECT TOP 100 PERCENT NUMCAMPAGNE AS Campaign_Num, OWNERNAME AS owner_name, STATUTPROSPECT AS STATUS, CAST(CONVERT(varchar(10), DATESELECT, 103) AS
datetime) AS call_date
FROM dbo.ProspectCampagne
WHERE (APPELE = '1')
this view shows from a prospect table for a given campagne, by date and by person used to call
2) here a view that uses the previous view to aggregate data: number of calls by date:
SELECT TOP 100 PERCENT Campaign_Num, call_date, SUM(nb_Total_Calls) AS nb_Total_Calls_day FROM dbo.Calls_Per_Campaign_Date_Status
GROUP BY Campaign_Num, call_date
ORDER BY Campaign_Num, call_date
3) here is a view that uses other views to agrregate data by data AND by person called:
SELECT TOP 100 PERCENT Campaign_Num, call_date, STATUS, SUM(Nb_Calls) AS nb_Total_Calls FROM dbo.Calls_Per_Campaign_Dt_Own_Stat
GROUP BY Campaign_Num, call_date, STATUS ORDER BY Campaign_Num, call_date, STATUS
etc.....
In SQL server DB; do the calculations:
We have views that gather, agregate data...
To know the performance of the server with 100k, 500k, 1M 2, 3, 4 million records
How to find that performacne. ????
Give me your advice.
Thanks
Babu B
View 14 Replies
View Related
Jan 19, 2001
Hi guys.
Is there anyway to increase the priority of a Service in NT services in processing like we have in SQL Server.
-MAK
View 1 Replies
View Related
Apr 1, 2006
We are designing the database for a high load web application.
We used 4 techniques.
1.No outer joins,No Unions, > only in reports
2.No joins in main forms
3.Choose the smallest dataTypes possible.
4.Used a single instance for all users.
Are there any additional MAJOR ones?
View 14 Replies
View Related
Aug 31, 2007
Hello,can Foreign Keys boost performance resp. Select or Where Statement in combination with a join?Silas
View 14 Replies
View Related
Nov 13, 2007
Hi
I have a question about the partitioning a table.
I have a database with more 50 tables and 25 tables are having more than 10 lakhs records which includes history records.I have two data files for this database under PRIMARY FILE GROUP.Now i want to transfer these history records to some other database.
I wanted to know if this kind of activity will boost the database performance?.If yes how should i configure my new database.
On what factors of partitioning my performance will boost.
Thanks in advance
Regards
Arvind
View 1 Replies
View Related
Sep 28, 2007
I have an existing database with a table of about 50 milion records. There are also about 20 other tables, but they are alot smaller. The large table has a uniqueidentifier as it's Primary key (not sequential) and a forien key to a 'parent' table. The table also has a column telling when it was created. So, a bit simplified, it looks like:
ChildTable
---------------
Id uniqueidentifier <PK>
ParentId uniqueidentifier <FK>
CreationDate DateTime
ParentTable
-----------------
Id uniqueidentifier <PK>
CreationDate DateTime
Most of the questions accessing the Child table (the large table) is doing so by referensing the parent table, and not the CreatingDate, i.e.
SELECT *
FROM ChildTable
WHERE ParentId = '......'
All records with a specific ParentId will have very similiar CreationDates.
Now, my question is, will Partitioning the ChildTable boost performance for me? In case it will, what column(s) would define the Partitions? If I do it by CreationDate, a select-query like the one above will have to scan all partitions anyway, doesn't it? Doing it by Id isn't soo easy either I guess? If it helps, it might be possible to change the primary keys in the tables to have sequential guids.
Is there perhaps a performance tool to get help with suggestions about how to partition the table? Something like the 'Performance dashboard' reports, but for partitioning?
Regards Andreas
View 10 Replies
View Related
Dec 8, 2005
Hi,
I've noticed declining performance on our main accounting server. We have a Windows 2003 server running SQL 2000 Enterprise and 8 GBs of RAM.
Has anyone used the "boost SQL Server priority on windows" before? If so, were there any major benefits in using it?
This server is excusively used for SQL btw...
Thanks,
DJ
View 3 Replies
View Related
Aug 18, 2005
Hello all,I have a strange problem that i need some advice on. I have the following field called FILENO. It is a SQL 2000 field with the Data Type set to Char (7). The following sql statement works perfectly:SELECT TOP 1 RTRIM(FILENO) AS TEST, RIGHT(DATEPART(Yy, FILENOYEAR), 2) AS YEAR FROM tblRecordsWHERE RIGHT(DATEPART(Yy, FILENOYEAR), 2) = '05'ORDER BY FILENO DESCIt returns the correct data, 0050. Now, what i'm trying to do is add 1 to the value so i can get the next available number which is 0051. But, when i run the following sql statement, i get 51 instead of 0051.SELECT TOP 1 RTRIM(FILENO+1) AS TEST, RIGHT(DATEPART(Yy, FILENOYEAR), 2) AS YEAR FROM tblRecordsWHERE RIGHT(DATEPART(Yy, FILENOYEAR), 2) = '05'ORDER BY FILENO DESCDoes anyone have an idea how to solve this? Thanks.Richard M.
View 1 Replies
View Related
Feb 28, 2001
I'm working as a consultant for a project and I have been having constant issues with the in house "database guru" about stored procedure formating.
He insists I use a format similar to this:
create procedure getUser @UserId int = NULL
as
select
UserName
, UserPassword
, UserRole
from
Users
join
UserRole
on
User.ID = UserRole.ID
where
User.ID = @UserID
and
User.Active = 1
where as I prefer this format:
create procedure getUser
(
@UserId int = NULL
)
as
select UserName,
UserPassword,
UserRole
from Users
join UserRole on User.ID = UserRole.ID
where User.ID = @UserID
and User.Active = 1
Now normally I don't argue about style issues but I find his format confusing and it bothers me that it senselesly wastes lines make the stored procedure longer than necessary.
I'd love to get some comments on this issue or reference to any discussions on issues such as this.
Thanks
View 2 Replies
View Related
Apr 9, 2008
Hello -
Does anyone know why SQL Server Reporting Services formatts SQL weird? -- If I type a SQL statement in notepad or notepad++ and then paste it in Reporting Services it will change the whole structure and "expand" it out to three or four times its size.
Thanks
Adam
View 3 Replies
View Related
Dec 12, 2006
Hello All,
Is it possible to format an integer to a float value with 2 decimals in an sql query.
If yes, Please help?
View 2 Replies
View Related
Jan 24, 2007
I need to show my SUM of the 2 columns added in the query below formatted as currency. Is this possible?
SELECT SUM(QVSTDN + QVNONC) AS Total FROM INVOICE_TBL WHERE QVORDN = @QVORDN AND QVINV = @QVINV
I tried:
SELECT CONVERT(varchar(12), SUM(QVSTDN + QVNONC) , 1) AS Total FROM INVOICE_TBL WHERE QVORDN = @QVORDN AND QVINV = @QVINV
But this does not format it as currency. Any input would be helpful.
View 6 Replies
View Related
Apr 25, 2007
I imported data from a text file, where dates are currently in the format: 050729 to represent July 29, 2005.
How could I write a sql statment to put make this show up as 07/29/05?
Thanks
View 5 Replies
View Related
Jan 31, 2008
I am trying to output data from SQL Server to an email body message with a little formatting (just to make it legible)
Using this SQL statement:
select top 1 'Name', app_first_name+' '+app_last_name [name] ,'Submission Date', submission_date ,'Address', app_address+', '+app_city+', '+app_state+' '+app_zip from vw_bpa_export where app_email = dave@email.com order by submission_date desc
the resulting output displays this in the email body
"Name","Brian Vogler","Submission Date","2008-01-30 07:45:00","Address","6285 Harrow Trace, Norcross, GA 30092 "
but what I want is the text to look more like this:
Name Brian VoglerSubmission Date 2008-01-30 07:45:00Address 6285 Harrow Trace, Norcross, GA 30092
Is there a way to do this in the SQL statement (without HTML tags)?
View 3 Replies
View Related
Jun 9, 2008
In a SQLDataSource, the following line throws an error as an undefined function. FilterExpression='left$(lname,1) = "D"'
Isn't this a standard VB function and why does it produce an error. How do I fix?
Thanks
View 2 Replies
View Related
Dec 13, 2000
Hi,
In MS Access I can do the following:
Format([SW_Dttm],"Short Date")
What is the equivalent in SQL Server?
Thanks,
Bill
View 1 Replies
View Related
Jun 19, 2000
I have values in my database that I'd like to display as $xxx,xxx.xx. Some of these values use the money datatype and others use float.
I'm building a string in a stored procedure that is eventually passed to Visual Basic. I want to format these values in the above format in the string that is passed. I've searched all through MSN and I couldn't find anything related to VB's FORMAT function in SQL Server.
Does Transact-SQL have any functions that could format these values for me?
View 1 Replies
View Related
Mar 13, 2001
Does anyone familiar with a tool or a quick way to put apostrophes and commas around some values, so they could be used in a query ? Example, if I have a bunch of zip codes such as:
10001
10003
10009
54321
89043
etc
Let's say I have a couple of hundreds of them. I need to be able to see them as -
'10001','10003','10009','54321','89043' etc
Appreciate very much your time.
Ivan
View 3 Replies
View Related
Feb 1, 1999
I am trying to run a query for the company that I work for and I am getting the right data but more than needed. the query is like this
select distinct(companyname),max(calldate) from tablename
what I want to see is the latest call made to a customer for each company
but what I get back is the company listed several times with the dates of each call. I only want the latest. Any help?
View 1 Replies
View Related
Apr 12, 1999
Hello Everyone,
I have been requested to take a number of columns and concatenate them into one column with a specifc format/layout. For example I need to take this, (noting all columns are of different datatypes:
col_1 col_2 col_3 col_4 col_5 col_6 col_7
PK #1 John Doe 123 Elm Street Anywhere NM 55660
#2
#3
etc..
...and create this on a different table (noting col_2 is of datatype "text":
col_1 col_2
PK #1 John Doe
123 Elm Street
Anywhere NM 55660
#2
#3
etc..
Can this type of formatting be done?
I know how to concatenate data and use the convert function, but how do you add in CR-LF to get the proper formatting??
Any help would be greatly appreciated.
Troy
View 1 Replies
View Related
Jun 12, 2001
Requirement:
On Insert, Trigger xp_sendmail to complete pre-formatted email with specific content and send to given email address.
Question:
What tools/methods are available to develop a template that SQL Mail's (SQL Server) xp_sendmail function can utilise without resorting to attachments.
e.g. - how do Amazon.com format and send their automated shipping confirmaion emails ??
View 1 Replies
View Related
Oct 10, 2001
I'm currently getting a date in this format 2001-10-08 10:35:45
(yyyy-mm-dd hh:mm:ss) how would I convert the date to this format
2001-10-08/10:35:45 (yyyy-mm-dd/hh:mm:ss)? The only thing added was the / between the date and time.
Thanks in advance,
Ed
View 2 Replies
View Related
Aug 6, 2004
Hi,
I need to know how i can format the date so that the query results will come out as the format mm/dd/yy hh:mm:ss?
Thanks alot.
View 1 Replies
View Related
Sep 8, 2006
I am new to php/MySQL and am working on an online manual. The data is divided into Title and Content. The title is easy as it is one line but the content is meant to be in a number of paragraphs.
The Content field is currently set as "blob" and the text comes out as just one paragraph. Wha can I do to get paragraphs etc into the text?
Thanks.
View 1 Replies
View Related
Oct 7, 2004
Hi, I am running a script which inserts certain rows into a table and at the end of the execution, I do a select statement to show the inserted data as output in the results pane and it is showing as truncated...How can I show the full results..
Here is my script to show the results.
----------
SET NOCOUNT ON
DECLARE @errorCount INT
SELECT @errorCount = COUNT(*) FROM error_report WHERE id != - 2 ANDid != - 5
IF @errorCount = 0
BEGIN
INSERT INTO error_report VALUES( '' , - 1 , 'No error found.' )
END
INSERT INTO error_report VALUES( '' , - 2 , 'The Report was generated on ' + CAST(CONVERT(VARCHAR(23), GETDATE(), 1) AS VARCHAR) )
GO
SELECT table_name + ' table has bad data at id = ' + CAST(CONVERT(VARCHAR(23), id) AS VARCHAR) + ' (' + CAST(reason AS VARCHAR) + ')'FROM error_report WHERE id > 0
SELECT table_name + ' table has bad data (' + CAST(reason AS VARCHAR) + ')' FROM error_report WHERE id = 0
SELECT reason FROM error_report WHERE id = - 1
SELECT ''
SELECT reason FROM error_report WHERE id = - 2
SET NOCOUNT OFF
GO
-------------------------
The Results in the bottom pane looks like this below
------------------
NODETABLE table has bad data (There are 2 duplicate subclass)
Propertytable table has bad data (at Parentid = 2000000859 and p)
Propertytable table has bad data (at Parentid = 10122 and proper)
-------------------
But, when I do a select, they are like this below
----
NODETABLE0There are 2 duplicate subclass name: Specification
Propertytable0at Parentid = 2000000859 and propertyid = 721
Propertytable0at Parentid = 10122 and propertyid = 9
View 5 Replies
View Related
Jan 18, 2005
I am using a MS Access ADP connected to SQL Server Data
In a view I have the following formula:
dbo.tblQuoteItem.Cost + dbo.tblQuoteItem.Markup * dbo.tblQuoteItem.Cost * .01
this calculates cost + markup
I cannot get it to format in Currency
Ex:
Cost is $1.75
Markup is 2.00 (2%)
Total shows - 1.785000
I want $1.78
Also - I am using this formula to calculate the Quoted price for the Qty Entered
dbo.tblQuoteItem.Qty * dbo.tblQuoteItem.Cost + dbo.tblQuoteItem.Markup * dbo.tblQuoteItem.Cost * .01
Using a Qty of 2 for above, I get 3.535000
I want $3.53
Any help is appreciated - AB
View 2 Replies
View Related
Jun 13, 2007
Hi,
I wrote the below code and procedure that exports two tables contents into 2 separate Excel files. Is there a way to export contents of two tables via BCP utility into 1 Excel file but 2 different Worksheets of this file?
DECLARE @FileName varchar(50),
@FileName1 varchar(50),
@bcpCommand varchar(2000)
SET @FileName = 'E:GPPD_db_stats.XLS'
SET @FileName1 = 'E:GPPD_file_stats.XLS'
print @FileName
SET @bcpCommand = 'bcp "master.dbo.spdbdesc" OUT ' + @FileName + ' -Samex-srv-gppdb -T -c'
print @bcpCommand
EXEC master..xp_cmdshell @bcpCommand
SET @bcpCommand = 'bcp "master.dbo.spfiledesc" OUT ' + @FileName1 + ' -Samex-srv-gppdb -T -c'
print @bcpCommand
EXEC master..xp_cmdshell @bcpCommand
exec master.dbo.xp_stopmail
set @bcpCommand = ' ' + @FileName + '; ' + @FileName1 + ''
DECLARE @body VARCHAR(1024)
SET @body = 'Please find enclosed files with the database status reports as of '+
CONVERT(VARCHAR, GETDATE()) + '. Please DO NOT respond to this email or the ones coming in the future ' +
'with data files as this email address is not monitored for incoming emails. However, if you have any ' +
'questions/concerns please contact ....'
EXEC master..xp_sendmail
@recipients='alla.levit@amex.com',
@message = @body,
@subject = 'Database Weekly Statistics Report',
@attachments = @bcpCommand
Thanks in advance!
-Alla
View 1 Replies
View Related
Jan 20, 2004
Hi, I was wondering if there is a way to output a special format for dates in SELECT statements. Currently, my date field is returning my date in this format:
2004-01-19 00:00:00.000
but I want it to be like this:
1/19/04 or 1/19/2004
Is there a way to do this?
Thanks.
View 9 Replies
View Related
Mar 13, 2004
I have an old DB2 app that has date values in 4 fields (i.e. Date1_MO, Date1_DA, Date1_CN, Date1_YR). I have several MSAccess queries that I convert this to a date by doing the following:
CDate(Date1_MO & "/" & Date1_DA & "/" & Date1_CN & Format(Date1_YR,"00"))
Piece of cake....however I am struggling with this in MSSQL.
Mostly I am fighting formatting the Year. As you can see, If I were to concatinate the above values i would come up with something like 3/9/204 for a date of March 9, 2004. (Each field is a numeric value).
I have gotten this far....
select CAST(Date1_MO as varchar(2))+ '/' + CAST(Date1_DA as varchar(2))
+ '/' + CAST(Date1_CN as varchar(2))+ CAST(Date1_YR as varchar(2)) as Date1
From tPrices
I still need to convert the whole string to a date, but more importantly, I cannot figure out how to get the last element (Year) to format as '04' instead of '4'. I can't concatinate a 0 in front of it for obvious reasons. (Athough I was tempted, just joking)
I looked through a lot of the T-SQL docs but have come up dry.
Anyway HELP!!!!!!
View 2 Replies
View Related
May 5, 2004
At first I'd like to justify myself - I'm rather a greenhorn in MS SQL ;-)
What I'd like to do is to create a very simple spool file from my database.
I'd like to do this using OSQL tool and it's very important for me to have the spool file in the same format as it was earlier when I used Oracle and simple spool command.
And it's almost the same, but white spaces...
There's always one white space at the begining of each line and at least one at the and of each line.
I've been trying many OSQL switches and ltrim(..) / rtrim(..) functions but the problem still exists...
The spool file is rather big - around 300MB so it's not good idea to use SED (for example) to remove white spaces.
So my question:
IS IT POSSIBLE TO REMOVE WHITE SPACES COMPLETELY USING OSQL SPOOL?
Thanks in advance for any help.
View 9 Replies
View Related
Apr 2, 2008
hey there I have a series of finacial reports. the girls that work on them want color and the boss wants them to print in black and white. the color back grounds print grayscale so I would like to add a link of some sort that changed the background to transparent when they want to print a report.
View 10 Replies
View Related
Apr 2, 2008
can anyone help me try to figure out how to get my colored background to print in white and not gray
View 1 Replies
View Related