Somebody can give some tips or hints for speed up my querys and procedures from sql and get more performance, what is best to use, joins o cursors, using cursors can give me more performance?
View 1 RepliesHi.
Me any my team are soon going to work on a performance critical application. My team has some experience of writing SQL, however we have not done performance oriented coding.
I am looking for a comphrehensive document which lists information for writing good SQL with performance. Please guide if there is such a document or web site.
Thanks,
Prasad
Hi,
I have several data bases on a server (SQL Server 2000 only, no web server installed) and lately, as the company keeps gowing, my users complain saying the server gets slow, (this dbs are well designed and recieve optimizations and integrity checks, etc) because of this, Im thinking about getting a new server to repleace my old ProLiant ML 330 which was bought 4 years ago but Im concerned about what server arquitecture or characteristic can help me best to improve response performance, is it HD speed? Processor speed? or more Ram? I want to make a good decision, so I´d really appreciate your help...
Thanks, Luis Luevano
I am querying my dBase "Reservations" to obtain a range of dates for
rooms in our hotel that are available for a vacationer to reserve...
this is the my present SQL structure (two tables(roomReserve & roomDescribe inner joined)
**********************
SELECT roomReserve.availibility, roomDescribe.numRooms
FROM roomDescribe INNER JOIN roomReserve ON roomDescribe.code = roomReserve.code
WHERE (((roomReserve.availibility) Between 8/20/99 And 8/23/99) AND ((roomDescribe.numRooms)>0));
****************************
The problem is that I do not want any room open dates (roomReserve.Availibility) to come up if any of the days in between the date range have 0 rooms available (in other words no room available on that date) (roomReserve.numRooms)>0
But they do because the query is not correctly written to be an all or none recordset for the date range time peroid…
For example, if I query for the date range of 8/20/99 to 8/23/99 using a criteria that the room must be available (>0), and let's say the date 8/21/99 has 0 rooms available for the King Fireplace room, then the query's results set gives me a list of the other 3 dates (8/20, 8/22, 8/23) for when the King Fireplace room is available...
I don't want anything thing for that date range to come out if any of the dates (within that range 8/20-8/23) have a room status = 0 during that time period...
I need a way to receive an all or none recordset result... with only one query statement…
I do not want to have to query a query… if that's at all possible…
thank you... chris james
I just started working with MS SQL. I'm attempting to find the equivalent of the MySQL commands STATUS and SHOW STATUS.
Essentially, I want to connect to the database from a .net app, check the status, such as how many connections, table locks, errors, etc... and display that information on my application.
Any recommendations?
select *
from OPS_TimeCards tc
join OPS_TimeCardHours tch on tch.TimeCardID = tc.TimeCardID
join OPS_Projects pj on pj.ProjectID = tch.ProjectID
where tch.TimeCardID = @TimeCardID
Union
select *
from OPS_TimeCards tc
join OPS_TimeCardExpenses tce on tce.TimeCardID = tc.TimeCardID
join OPS_Projects pj on pj.ProjectID = tce.ProjectID
where tce.TimeCardID = @TimeCardID
I have this query but when i try to use the UNION i get this error
Msg 205, Level 16, State 1, Procedure GetTimeCardSearchResults, Line 24
All queries combined using a UNION, INTERSECT or EXCEPT operator must have an equal number of expressions in their target lists.
I need to run both of these queries but have them return one result set....anyone know how to do???
Ok in Replication for sql 2000,'
what happens when a query such as 'update tableA set a=1 where id=5' affects 1 row. What will happen when it does not affect any row.
Aaron
Hello,
I've got a trouble with my query and left outer joins. I've got 2 tables, table A and B, both have the same record called ID. I used this query:
SELECT *
FROM A LEFT OUTER JOIN B ON A.ID = B.ID
This is OK and works fine, my trouble comes when i have a duplicated ID on both A and B. Instead of return 2 fields, it returns me 4.
Is there some way to force SQl server to return only the first founded on B, but the 2 duplicated IDs on the A table?
Thanks for all.
I have two databases with table structures exactly the same. The query in one database works and returns correctly. The same query (copied and pasted) in another database returns the correct records but completely ignores the order by. Even stranger, when you are creating the query under modify and run it, it is fine. But when I right-click on the query and go to 'Open View' then the query does not return correctly. Below is the query:
SELECT TOP (100) PERCENT pf.description AS incentive, CAST(i.targetc AS FLOAT) AS targetc, i.targetp, b.basis, d.description, i.iSelect, i.direction, pf.pValue,
pf.pFigure
FROM dbo.PerfFactorWeights AS pfw INNER JOIN
dbo.PerfFactors AS pf ON pfw.incentiveID = pf.incentiveID INNER JOIN
dbo.Incentive AS i ON pfw.incentiveID = i.incentiveID AND pfw.basis = i.basisID INNER JOIN
dbo.Basis AS b ON i.basisID = b.basisID INNER JOIN
dbo.Division AS d ON pfw.division = d.uniqueID
ORDER BY incentive, d.uniqueID, b.basis
Hi there!I'm programming in delphi and new to querieng MS SQL Server. Is there a wayto monitor the queries sent to the server and something of the returns. Atleast execution time would be interesting ...Thx in advance,Fritz
View 2 Replies View RelatedSomeone asked me a curious question.Can I execute a query to sql server from a bat file? how?If not is there a simple scripting laguage that this person might use todrive his process that is similar to a dos bat file?
View 5 Replies View RelatedHello,Could some clarify for me? I’ve been told that I should add lock statements to my database write querys (update, delete, insert). I’m not sure If it’s necessary though. The only documentation I’ve found so far is how to implement a lock statement with threading but I’m not using the threading namespace. What best practice and how would I implement this without threading or should I be using threading?Thanks in advance!
Hi,
I'm looking for a solution to run querys (create databases ed) after a silent installation of SQL 2005 Express edition (example with a .sql file). So users can run a installation unattended with all databases installed and so on.
What I got is a document about how to run a silent installation (document " Using Command Prompt Options to install SQL Server Express") but this docs there's no solution how to run querys after the installation.
Thanks
Robert-Paul
This is driving me a little nuts, I have been testing locally with SQL Server Express 2005 and this has not been a problem, however now that I have restored the databases to SQL Server 2005 (not express) When I go into SQL Server Management Studio open a table the option for changing a query type is greyed out has anyone seen this or have any ideas how I can get this working.
Thanks,
Terry
I have a column colC in a table myTable that has a value (e.g. '0X'). The position of a non-zero character in column colC refers to the ordinal position of another column in the table myTable (in the aforementioned example, colB).To get a column name (i.e., colA or colB) from table myTable, I can join ("ON cte.pos = cn.ORDINAL_POSITION") to INFORMATION_SCHEMA.COLUMNS for that table catalog, schema and name. But I want to show the value of what is in that column (e.g., 'ABC'), not just the name. Hoping for:
COLUMN_NAME Value
----------- -----
colB 123
colA XYZ
I've tried dynamic SQL to no success, probably not executing the concept correctly..Below is what I have:
CREATE TABLE myTable (colA VARCHAR(3), colB VARCHAR(3), colC VARCHAR(3))
INSERT INTO myTable (colA, colB, colC) VALUES ('ABC', '123', '0X')
INSERT INTO myTable (colA, colB, colC) VALUES ('XYZ', '789', 'X0')
;WITH cte AS
(
SELECT CAST(PATINDEX('%[^0]%', colC) AS SMALLINT) pos, STUFF(colC, 1, PATINDEX('%[^0]%', colC), '') colC
FROM myTable
[code]....
Employee Date Enquired Title Company
Bryan Cole 2007-05-01 Recruiter ABC
Bryan Cole 2007-05-15 Recruiter ABC
Bryan Cole 2007-05-21 Recruiter ABC
Bryan Cole 2007-06-15 Recruiter ABC
Bryan Cole 2007-07-01 Recruiter ABC
Bryan Cole 2007-07-30 Recruiter ABC
I have to do row by row date comparisons in a date column. If the date difference is more than 30 days we keep it , otherwise we suppress it. How can we write the query without using cursor so that only the bold rows will come ?
What is it mean GHOST in SQL Trace column ?
Thanx in advance.
Hi Gurus ..
Could any one create a thread called Tips for the Day and Give any imports and complex query to learn on each and every Day ?
Thanks in Advance
Dana
Dear all,I'm designing a system including the database and the securityrepresents the most crucial aspect of the system; hence for thedatabase security i have implemented the following aspects and needyour advise on further aspects or perhaps corrections where by thesystem is web based using asp.net and under iis 6.0 with https; in theasp.net engine side, i have included client-side validations for whatever is inputed and validated against sql injections for postbackforms:The features of security in SQL Server 2005 side i have implemented:1.) Created MACHINEASPNET Account2.) Allowed ASPNET Account to access the DB3.) Explicity denied ASPNET Account all permissions to all tables,functions and views4.) Denied all permissions to the ASPNET user for stored proceduresexcept EXECUTE permissions5.) By Stored procedure creation, WITH ENCRYPTION, EXECUTE AS'MACHINEASPNET' was usedNo SQL was included in the asp.net code except for calling storedproccedures; the policy is to only call stored procedures within theasp.net pages and encrypt the connection strings inside the web.configfile.Kindly, give me some guidelines for better security or discuss with methe security aspects i mentionedRegards
View 1 Replies View Related
hi,
pls help me ,i am going to attend the interview for SQL DBA pls give me a some tips.
Hi all:
I'm new to replication but I have already set up replication and have seen it
working and failing and have gotten myself out of jams so far but there must
be an easy way to administer it when things don't replicate as expected. I'm
finding that I could easily kill half a day just trying to dig up
information leading to troubleshooting tips. Is there documentation just on
managing this feature. The regular MS Administrator's guide doesn't offer much.
Currently I have a problem that if replication fails on one command I get a
SQL Mail telling me of the problem but does replication continue to the next
command or does it just stop until the problem is fixed? I'm finding that I am
constantly checking the publisher and subscriber databases and verifying if
replication is indeed doing what the msjob_commands table reports. I set the
batch to commit after each transaction instead of every 100.
Help...
Hi,
For your day-to-day SQL Server issues like query tuning, optimization, TSQL problems, I am writing the blog called
http://blog.namwarrizvi.com
Some of the latest articles are:
Generating 1 million rows in less than a second
Conditionally add column in the table
Multiple Inserts in one statement
Capture every data operation in SQL Server 2008
100 Nano seconds precision in SQL Server 2008
Represent Trees and Graphs in TSQL
MERGE Statement of SQL Seerver 2008
Return Last n Orders by using APPLY operator
Number padding in TSQL
Microsoft Performance Point Server and Sharepoint
Caching and Recompilation in SQL Server 2005
and many more....
I will really appreciate comments and suggestions.
Hello,
We come accross situations where people are running big updates on the database (i.e. 50.000 updates).
Our problem is that those big updates are blocking other user updates.
Thanks to snapshot isolation, users can query (select) the database with no lock.
We rebuilt the indexes setting that the indexes used by the update procedure would not use page locks and only row locks.
We set that the database would update the statistics asynchronously.
Now we are still facing blockings and we would like to optimize the database to avoid those blockings.
What else could we check? Any tips regarding the way to avoid that problem is really welcome.
Thanks,
Olivier
Hello I have 2 sql servers in my company and many remote sites. I amtrying to figure out the best way to keep them safe, since both haveaccess to the internet behind the firewall. I was planning to disbablethe default gateway on one or maybe disabling file sharing on both, iwas also thinking to block access to the the terminal server that isrunning in admin mode, either through the firewall or the permissionsof the rdp protocol. I have a few admins that have account manager andserver operator permission as well as exchange admin. Any ideas in howto restrict access to my servers? thanks.
View 1 Replies View RelatedHi everybody,
i would be thankful if anybody posts some references of the web sites
which relates to SQL Server Performance tuning.
Thanks in Advance
Suresh Kumar Goudampally
I've created a few indexes on my tables but before I over-do it I wanted to see if there were any good websites out there with recommendations. My plan is to create clustered indexes on my primary keys and non-clustered for each foreign key. Also there are a few fields that are regularly searched so I will index them separately as well.
I don't want too many though because I know that affects the performance of record inserts. I'm also not sure about using multiple keys in one index.
Is there a good site out there with tips on what indexes to create or avoid, or have I pretty much covered it?
Hello everyone!
I've got a problem with a real slow query, I would be very happy if somebody has any idea to improve the speed of it...
The idea is to get the top 2 products, a customer hasn't bought wich are in his interest...
query (simplificated)
-------------------------------------------------
SELECT TOP 2 prodID, Title, Price FROM bestSold7Days WHERE
prodID NOT IN (SELECT prodID FROM orders INNER JOIN orderProducts ON orders.orderID = orderProducts.orderID WHERE (orders.custID=394))
AND
(prodType = COALESCE((SELECT TOP 1 products.prodID FROM orders INNER JOIN orderProducts ON order.orderID = orderProducts.orderID INNER JOIN products ON orderProducts.prodID = products.prodID WHERE (orders.custID=394) GROUP BY products.prodType ORDER BY SUM(orderProducts.PCS) DESC), 2))
-------------------------------------------------
end query
(COALESCE is for replacing if the customer hasnt ordered anything, or hasnt ordered anything of this type)...
Thanks for any time spent!
please post your idea, experiences what is fastest way to maniulate ms sql server using vb6 .
thanks...
Are there any best practices for indexing to support queries with MIN() and MAX() in them? what if MIN() and MAX() are partitioned? Super bonus question: what if MIN() and MAX() are not only partitioned, but are called on a field in a derived table, and one of the partitioning elements comes from a table that's being joined in the derived table?
I experimented with inserting the derived table into a temp table, putting a POC index on that, and querying out, but that actually took longer.
We are coming out of the dark ages with our app using SQL 7 and, following the excellent advice of the folks here on SQLTeam, installing SQL 2005 Express on our new webserver.
Not being terribly fluent in all things SQL, I was wondering if anybody could provide input on the best practices for getting SQL 2005 Express going on the new server.
So far I've:
- Installed SQL 2005 Express
- Downloaded and "installed" the SSEUtil for CMD line instructions
- Downloaded and installed the graphical management interface (very nice, makes me feel more comfortable - like SQL 7 console!)
- Copied backup files (made using SQL backup maintenance) to the new server
Should I simply create an empty db of the same name on the 2005 server and then restore the 7 data? Or ????
I searched briefly for previous posts of this nature and didn't find too much info so I hope I'm not duplicating effort here...
Thanks in advance for any advice!
Mmmmmkay. Yeah, did you get the memo about the TPS reports?
I need to convert an excel matrix into a table. Currently, the data consists of months going across the top and business names going down the left side. Each business name has three rows of data per monthly column, such that there are three numbers in the january column, three in the february column, etc. etc.
I want to convert to a table that has five columns, the business name, date, and the three data columns.
Any help would be greatly appreciated. As of right now I'm staring at keying in about 2000 rows of data by hand.
Our parts table has 5k records. I want to use part number as a parameter for one of my reports. Is there a way to do this and have the report generate in a reasonable amount of time?
Thanks
Hi, All experts here,
Thank you for your kind attention.
I have questions about Slowly Changing Dimensions. I am quite confused about when should we use type 1 ( changing), type2 (historical), or type3( fixed) for the dimensions in each table? Is there any good suggestions on that?
Thank you in advance and I am looking forward to hearing from you.
Yours sincerely,