Need SQL Server 2005 tool similar to Oracle's Connection Manager

I would like to know if there is a comparable tool in SQL Server 2005 to the
Oracle Connection Manager (link to Overview on Oracle product below)
http://www.oracle.com/technology/products/oraclenet/htdocs/cman_overview.htm
Hi
"Jason" wrote:

> I would like to know if there is a comparable tool in SQL Server 2005 to the
> Oracle Connection Manager (link to Overview on Oracle product below)
> http://www.oracle.com/technology/products/oraclenet/htdocs/cman_overview.htm
>
Apart from connection pooling I don't know of any third party product that
does anything like this.
For example see http://msdn2.microsoft.com/en-us/library/ms716319.aspx
John
sql

Need SQL Server 2005 tool similar to Oracle's Connection Manager

I would like to know if there is a comparable tool in SQL Server 2005 to the
Oracle Connection Manager (link to Overview on Oracle product below)
http://www.oracle.com/technology/pr...an_overview.htmHi
"Jason" wrote:

> I would like to know if there is a comparable tool in SQL Server 2005 to t
he
> Oracle Connection Manager (link to Overview on Oracle product below)
> Apart from connection pooling I don't know of any third party product thatdoes anything like this.For example see [url]http://msdn2.microsoft.com/en-us/library/ms716319.aspx" target="_blank">http://www.oracle.com/technology/pr...y/ms716319.aspx
John

Need SQL Server 2005 tool similar to Oracle's Connection Manager

I would like to know if there is a comparable tool in SQL Server 2005 to the
Oracle Connection Manager (link to Overview on Oracle product below)
http://www.oracle.com/technology/products/oraclenet/htdocs/cman_overview.htmHi
"Jason" wrote:
> I would like to know if there is a comparable tool in SQL Server 2005 to the
> Oracle Connection Manager (link to Overview on Oracle product below)
> http://www.oracle.com/technology/products/oraclenet/htdocs/cman_overview.htm
>
Apart from connection pooling I don't know of any third party product that
does anything like this.
For example see http://msdn2.microsoft.com/en-us/library/ms716319.aspx
John

Need Performance Tips - Zip Code Locator

I have a stored

procedure used to lookup ad's similar to ebay. We want to allow the customer to

search based on their zip code in relation to the items location. This query

works but it takes too long. We only want to return the top 100 records. It also

uses indexed searching for the main search terms. Any ideas what we can do to

improve performance?

CREATE PROCEDURE Search
@.SearchText

varchar(200),
@.CategoryID int = Null,
@.TxtDesc bit = 0,
@.PriceMin money

= 0,
@.PriceMax money = 250000,
@.ZipCode varchar(5) = Null,
@.Distance

smallint = 0,
@.ManMul int = 1000,
@.ModMul int = 1000,
@.TitMul int =

100,
@.DesMul int = 1
AS
BEGIN
DECLARE @.CenterLat float
DECLARE

@.CenterLon float

-- Earth Radius In Miles
DECLARE @.EarthRadius

float
SET @.EarthRadius = 3958.76

-- Determine Lat/Lon For User's Zip

Code
SELECT @.CenterLat = Lat,
@.CenterLon = Long
FROM

List_ZipCodes
WHERE Zip_Code = @.ZipCode

DECLARE @.CntXAxis

float
DECLARE @.CntYAxis float
DECLARE @.CntZAxis float

SET @.CntXAxis

= cos(radians(@.CenterLat)) * cos(radians(@.CenterLon))
SET @.CntYAxis =

cos(radians(@.CenterLat)) * sin(radians(@.CenterLon))
SET @.CntZAxis =

sin(radians(@.CenterLat))

SELECT

TOP(100)
C.Classified_ID,
Manufacturer,
Model,
Title
FROM

Classifieds C
INNER JOIN Classifieds_Categories ON C.Classified_ID =

Classifieds_Categories.Classified_ID
LEFT OUTER JOIN

FREETEXTTABLE(Classifieds, (Manufacturer), @.SearchText)AS f1 ON C.Classified_ID

= f1.[Key]
LEFT OUTER JOIN FREETEXTTABLE(Classifieds, (Model), @.SearchText)AS

f2 ON C.Classified_ID = f2.[Key]
LEFT OUTER JOIN FREETEXTTABLE(Classifieds,

(Title), @.SearchText)AS f3 ON C.Classified_ID = f3.[Key]
INNER JOIN

List_ZipCodes AS ZC ON ZC.Zip_Code = C.Shipping_FromZip
WHERE

((COALESCE(f1.[Rank], 0)* @.ManMul) + (COALESCE(f2.[Rank], 0)* @.ModMul) +

(COALESCE(f3.[Rank], 0) * @.TitMul)) > 500
AND Active = 1
AND

Price_Asking >= @.PriceMin
AND Price_Asking <= @.PriceMax
AND

(Category_ID = COALESCE(@.CategoryID, Category_ID) OR Category_ID IN (SELECT

Category_ID FROM List_Categories WHERE Parent_Category_ID = @.CategoryID))
AND

Shipping_FromZip Is Not Null
AND (@.EarthRadius * acos((cos(radians(ZC.Lat))

* cos(radians(ZC.Long)))*@.CntXAxis + (cos(radians(ZC.Lat)) *

sin(radians(ZC.Long)))*@.CntYAxis + (sin(radians(ZC.Lat)))*@.CntZAxis)) <=

@.Distance
ORDER BY ((COALESCE(f1.[Rank], 0)* @.ManMul) + (COALESCE(f2.[Rank],

0)* @.ModMul) + (COALESCE(f3.[Rank], 0) * @.TitMul)) DESC
END

The ultimate cost for this query is coming from the FTS query. Basically, sqlserver will have to wait for the result to come back for each freetexttable() before it can join to the base table. And also, the resultset returned from the freetexttable() is not indexed. So, if there are lots of data in the resultset, the main query can take a long time to join. In case you have lots of data return from FTS, you might want to create a temp/working table to hold the data. You can then index them before joining with the base table.

Need Opinions on Updating Large tables

I have an incomming table that is similar to the following...
locationNbr, SalesDate, SalesAmt, TranCode, SalesQty
I have to update a table that is formated as such...
locationNbr, historyType, Year, D001, D002, D003, D004... --> D365
I am running a join from the incoming table to the table I need to update to
determine what records I need to Insert.
Then I am using a VB app to create a batch of update statements (because I
couldnt figure out how to dynamically specify columns in T-sql) for those
whose key records already exist.
I would imagine this would run alot faster if I could figure out a way to do
this within an sproc.
Is there a good way that I can dynamically build a SQL Update statement
within an sproc?
And, as I am not extremely familiar with MSSQL, is there any other fast
update methods when you are updating large tables?
Thanks for any input.Jace wrote:
> I have an incomming table that is similar to the following...
> locationNbr, SalesDate, SalesAmt, TranCode, SalesQty
> I have to update a table that is formated as such...
> locationNbr, historyType, Year, D001, D002, D003, D004... --> D365
>
You can use dynamic SQL, but I'm not sure it's going to provide any
improvements over all the processing your going to have to do to move
that nicely normalized table into one that looks strangely denormalized.
What's the story behind the denormalized table? What about leap years?
They have 366 days.
Check out sp_executesql in BOL for more information about how to build
dynamic SQL statements from in T-SQL.
David Gugick
Imceda Software
www.imceda.com|||Well, actually it has 371 columns, but it only uses the last 7 col every 9
years. The reason for the denorm format is for speed in accessing the data
for a forecasting method. This system forecasts 100'000+ skus in several
thousand locations, and I am told that using this table vs a nomalized table
increases the speed a significant amount.
Thanks for your input. And um... What is BOL ? :)
"David Gugick" wrote:

> Jace wrote:
> You can use dynamic SQL, but I'm not sure it's going to provide any
> improvements over all the processing your going to have to do to move
> that nicely normalized table into one that looks strangely denormalized.
> What's the story behind the denormalized table? What about leap years?
> They have 366 days.
> Check out sp_executesql in BOL for more information about how to build
> dynamic SQL statements from in T-SQL.
>
> --
> David Gugick
> Imceda Software
> www.imceda.com
>|||BOL is Books Online - the help documentation that comes with SQL Server.
The denormalized table design is potty and will not scale. Analysis Services
and indexed views are tools designed for this kind of work and can routinely
handle 100s of millions of rows of data.
David Portas
SQL Server MVP
--|||Jace wrote:
> Well, actually it has 371 columns, but it only uses the last 7 col
> every 9 years. The reason for the denorm format is for speed in
> accessing the data for a forecasting method. This system forecasts
> 100'000+ skus in several thousand locations, and I am told that using
> this table vs a nomalized table increases the speed a significant
> amount.
> Thanks for your input. And um... What is BOL ? :)
>
David is right. While denormailzed tables have been used for years in
read-only formats for reporting, it makes sense here to test a properly
designed, nomalized solution before commiting to this one.
David Gugick
Imceda Software
www.imceda.com|||Thanks for the input. Im going to do some testing on this in a denorm table
.
It appears the row count will be around 6.5 billion. Do you think that I
may run into trouble with that many records? Or will I have to split
different history types into different tables?
"David Portas" wrote:

> BOL is Books Online - the help documentation that comes with SQL Server.
> The denormalized table design is potty and will not scale. Analysis Servic
es
> and indexed views are tools designed for this kind of work and can routine
ly
> handle 100s of millions of rows of data.
> --
> David Portas
> SQL Server MVP
> --
>|||You'll almost certainly want to look at Analysis Services and Partitioned
Views for this.
Billions of rows are not uncommon - with the right hardware there is no
problem in principle with tables of that size. You should probably consult o
r
hire someone with AS and VLDB experience though.
David Portas
SQL Server MVP
--