Optimal SQL

Jul 23, 2005

Please excuse what is probably a no-brainer, but here goes.

Is there any difference, in terms of performance or any other pertinent
factor, between:

SELECT * FROM tblCustomers INNER JOIN tblCustomerOrders ON
tblCustomers.fldCustomerID = tblCustomerOrders.fldCustomerID

and

SELECT * FROM tblCustomers, tblCustomerOrders WHERE
tblCustomers.fldCustomerID = tblCustomerOrders.fldCustomerID

I note that if I type the latter into the SQL pane in a Data window,
SQL Server replaces it with the former.

TIA

Edward
--
The reading group's reading group:
http://www.bookgroup.org.uk

View 3 Replies


ADVERTISEMENT

Optimal Database Design

Mar 2, 2005

What is more efficient for a database design - a lot of tables with only a few records or a few tables with lots of records.

I'm starting a new site and each user will have numerous records but I'm not sure whether to have a few very large tables (over 100,000 rows) or start a new table for each user which would result in approx 1500 tables most of which would be the same table design with different rows.

I'm using SQL2000.

I guess this is quite a basic question, but I'm a bit unsure.

Any references anyone could point me too as well.

Thx

View 3 Replies View Related

Optimal Installation On Win 2003

Feb 3, 2004

Hi

I'm fairly new to this, so bare with me...

I have to make a new installation of an MS SQL 2000 EE on a Windows 2003 Std. Edt.

HW:
---------------------
Dual Xeon 2,4 + 1 GB Ecc
1 x 32 MB Adaptec 2100S RAID Controller
2 x 18 GB 10K HD
4 x 18 GB 15K HD
---------------------

So far I have made following configuration....
---------------------
2 x 18 GB 10K HD / RAID 1
- C:OS
- D:MSSQL program files + System DB's (Master, pubs ect.)

4 x 18 GB 15K HD / RAID 5
- E:TempDB
- F:Data + Logs
---------------------
But I'm not sure that this is the optimal configuration, and I'm willing to start all over :)


So my q's are.......
--------------------
Which RAID configuration would you suggest?
Which partitions on the raids would you suggest?
Which usage would you assign the various partitions?
How do I move the system and temp db's?
--------------------


Thanx!

Regards,

Taras Bredel dk

View 3 Replies View Related

Optimal Solution For Getting Timestamp Of Last Entry

Jul 25, 2007

Hi

I'm trying to find the optimal way of getting the timestamp of the last updated entry in an mssql database. A database is updated only about 5 times a minute, how ever a request for the time of the last entry could be around 1 per second. For this reason i was thinking of having a separate table which has a single row which is updated everytime a new entry is updated in the main table. I would then only need a simple SELECT statement and need very little processing power.

Is this the best method, or can you think of any others i could use?

many thanks

View 14 Replies View Related

100% Buffer Cache Hit Ratio Not Always Optimal?

Jun 19, 2008

I am wondering if 100% buffer cache hit ratio is considered not good in general?

Are there instances that it is actually bad and can contribute to server performance degradation?

Any thoughts on the topic most welcome :)


--------------------
keeping it simple...

View 11 Replies View Related

Optimal Configuration For Report Generator

Jul 23, 2005

I am working with a report generator that is based on SQL Server 2000 anduses ASP as the UI. Basically we have a set of reports that end users canexecute through a web browser. In general the model works fine, but we arerunning into some scaling issues.What I'm trying to determine is, what is the optimal configuration for thissystem. It is currently a 2.4G Pentium with a large RAID and 1G of RAM. Wehave been using the "fixed" memory configuration, allocating 864M to SQL.This is on a Windows 2003 server box.This works fine when a "small" query or two is executed, but the performancesuffers terribly when several users try to run reports in parallel. A singlequery might take 10 minutes to run if nothing else is happening on the box,but if additional users log on an run reports, it's almost impossible topredict when the queries will finish.I am also looking at the effect of database size on performance, runningtests against a database with 1 month, 3 months, and say 12 months of data,running the same query against 2 databases in parallel. With the originalconfiguration, the results were all over the place, with the 12 monthdatabase outperforming the smaller dbs, while other times there was littledifference. It seems that once the system starts paging, and paging heavily,it's over; the system never "recovers" and queries that previously ran in afew minutes now take hours.I added 3 G more memory to the system, and modified boot.ini to include the/3GB switch. Now when I run the same tests, the results are much moreconsistent, as the system rarely ever has to swap. Then again I've neverseen it go past 1.7G in Task manager, making me think that any more than say2.5G of memory is a waste?Things we are trying to determine are:- in the SQL Server memory configuration, is Fixed better than Dynamic? Wehave read that Dynamic is not good at returning memory to the OS once it'sbeen allocated- What else can we do to optimize the performance for this application? Itseems to me if the indexes are properly designed, the database sizeshouldn't have that much impact on performance, but this appears to be trueonly to a point. In comparing the execution plans between say a 12 month anda 3 month database, the plans are sometimes dramatically different. I assumethis is due to the optimizer deciding that going directly to the base tablesand not using an index will result in better performance, when in reality,this doesn't always appear to be true.- Are there other SQL Server switches I should be tweaking? Is there somenumber of simultaneous queries that this configuration should be limited to?- What about other versions of SQL Server (e.g. Enterprise, Data Center,etc) would these buy us anything?Thanks for any advice,-Gary

View 2 Replies View Related

Optimal Search For The Nearest Date

Nov 23, 2005

I have the following tableCREATE TABLE Readings(ReadingTime DATETIME NOT NULL DEFAULT(GETDATE()) PRIMARY KEY,Reading int NOT NULL)INSERT INTO Readings (ReadingTime, Reading) VALUES ('20050101', 1)INSERT INTO Readings (ReadingTime, Reading) VALUES ('20050201', 12)INSERT INTO Readings (ReadingTime, Reading) VALUES ('20050301', 15)INSERT INTO Readings (ReadingTime, Reading) VALUES ('20050401', 31)INSERT INTO Readings (ReadingTime, Reading) VALUES ('20050801', 51)INSERT INTO Readings (ReadingTime, Reading) VALUES ('20051101', 106)GO-- list the tableSELECT ReadingTime, Reading FROM ReadingsGOIt is a table of readings of a free-running counter that istime-stamped. I need to determine the value of the reading thatcorresponds to the closest date to the supplied dateAre there more optimal/efficient ways of accomplishing this than thefollowing?DECLARE @when DATETIMESET @when = '20050505'SELECT TOP 1 ReadingTime, Reading FROM ReadingsORDER BY abs(DATEDIFF(minute, ReadingTime, @when))The above gives me the desired result of ('20050401', 31).Any suggestions would be appreciated

View 1 Replies View Related

What Is The Optimal Configuration For A Multicore System?

May 14, 2007



I would like to know what options are available from BIOS / OS / SQL and server perspective when configuring or tuning a system with SQL Server 2000 or SQL Server 2005.



For example, I have a system with 4 dual-core Opteron CPUs on Windows 2003 Enterprise Edition. However, the OS sees 8 CPUs -- is this the optimal configuration or is it better (if even possible) to configure the system to see only 4 CPUs? The reason for this concern is due to performance problems faced deploying systems with Hyperthreading Technology.



Any documentation or examples in this regard would be very useful. Basically, what are the scenarios that would require a certain type of CPU configuration over another.



Thanks in advance for your help,



Ziggy

View 1 Replies View Related

File Placement For Optimal Performance?

Aug 24, 2007

What is the best performance for this configuration:

Files:
Data
Log
Indexes
tempdb


Disk:
A - RAID 10
B - RAID 10 (or should this be RAID 1?)

Whats best?:
A - Data and Indexes
B - tempdb and Log

??? Thanks.

View 1 Replies View Related

SQL 2012 :: Optimal Settings For DBCC Checks?

Aug 5, 2014

I have a VM set up for offloading DBCC checks. Specs are below. I've read through this, but I'm not seeing the performance gains by enabling the trace flags and using the physical only switch.

Is the whole drawback that I'm on SATA storage? Is there a VM configuration with the CPU I can/should change? I've been playing with MAXDOP trying to see if I can get any benefits but I'm not seeing a much.

wait_type wait_time_spctrunning_pct
CXPACKET 561191.4228.7128.71
OLEDB 387136.7619.8148.52
PAGEIOLATCH_SH 340674.5817.4365.95
TRACEWRITE 321598.8416.4682.41

[code].....

View 9 Replies View Related

SQL 2012 :: Optimal Combination Of Tools For Flexible Reports

May 28, 2014

I would like to build a report with nice functionalities like filter, sorting, drill-down, something like a PowerPivot Table, but with some layout/design/format capabilities. I would also want to publish the report, refresh it let´s say once a week, notify users when a new version is available, etc.

If I use PowerPivot, then I am not able to customized the report or to mix data from different sources in one table.

If I convert the cells of the PowerPivot table to workbook formulas I lose the filter, sorting, etc functionalities.

I still have to try using Reporting Services, but I think that always something is missing.

View 1 Replies View Related

Need An Optimal Way To Merge External Dataset To Database Values

Jan 3, 2008

C# .Net Application as front end
Sql Server2000 as back end

I need to merge an external dataset from .Net app(in XML format) with the information in database with one column in database table as the merging criteria. A situation similar to Left Outer Join, wherein i need all records from external dataset and if matched in database the corresponding values from there too, the only difference here is that the join is not between two Tables its between a table and external dataset.
There is no need to store the external dataset in the database in persistent form, its just a query - merge - response operation.

So, can anyone suggest the best possible solution for this? A table variable / temporary table / some other schema, what and how?

Thanks in advance..

View 8 Replies View Related

What Is The Optimal Solution To Find, If All The Data Is Entered On A Predefined Condition?

Dec 10, 2006

Hi,I want to know the optimal solution, to find if all the data was entered. Lets say, Table A (date field) and for a given month, i need that all the days in the given month are present in the Table A. Right now i have different solutions, 1) a stored procedure which loops through all the days in the given month against a select statement on Table A2) a stored procedure, create a temp table which contains all the dates in the given month, and a single select statement using where condition (select * from.... where datefield not in (select * from...))I want to know what is the best solution of these two or any other solution.Thanks 

View 9 Replies View Related

Managing Connections For Optimal Performance Question, Switch From Oracle To SQL

Feb 20, 2004

I was told in one of my systems classes that the real performance bottleneck in accessing information from the database was the opening of a connection from the application to the database.

To combat that problem I was advised to use a Singleton Factory pattern and to have that Factory instaniate a connection and open it, then pass references to that connection for all of the objects that it created. All of those objects passed the connection reference to the objects they created and so on. Basically that meant that I only ever had one connection open at any one time for my entire application. And I was able to implement this solution at my previous job where I was developing in Oracle. I primarially used OracleCommands and OracleDataReaders to get the informaton into and out of the database. I thought this was a very nice solution. Having this many DataReaders accessing a single connection was not a problem because OracleConnections don't get locked from having more than one DataReader open at once.

At my current job, however, I use SQL Server. I am concerned that the single connection will not work in my new enviroment as the SQLDataReaders lock up the connection while they are using it. If the information that I recieved about opening connections being the real bottleneck, then I am hesitant to have a connection instanciated and opened for each method, but I am concerned that a whole lot of errors will be generated if I use the single connection method. Also, how do DataAdapters effect my decision of which approach to use.

Any advice would be most helpful. If you have any questions that would help answer just ask. Thanks.

View 4 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved