|
HI Guys need ur advise here
I am facing this issue that when i use the sp_executesql to execute my statement, the query time is way longer then normal.
Previously i am using this methed in stored procedure
simpely coding the statement
SELECT * FROM Bla Bla
but when i change to using
DECLARE @SQL AS NVARCHAR(MAX)
SET @SQL = 'SELECT * FROM bla bla Table'
EXECUTE sp_executesql @SQL
the query time slow down greatly
i change to using sp_executesql because i need to create the STATEMENT base on some condition checking
will be gald if u guys have any advise
Thanks a million
KaKaShi HaTaKe
|
|
|
|
|
This might be because SQL is not choosing the best execution plan. Check the execution plan which is followed to execute that dynamic query. Although, I am not aware that there is much you can do about this (except for getting rid of dynamic queries if possible), you can check out parameter sniffing if it can help. Here[^] is an article that tells about it.
|
|
|
|
|
Everyone keeps telling me that there is little or no difference between a stored proc and dynamic SQL, then I come across a post like this one and I think they are full of bullshit.
Change your var from nvarchar(max) to varchar(8000), it may help and cannot hurt.
REALLY make sure that you must use dynamic sql, there is often a way to get around using it if you construct your query correctly. Using case, conditional where and even if clauses to avoid dynamic sql.
As danish suggested, take a look at the execution plan of the dynamic and a normal version of the proc and see where you are paying the penalty.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Mycroft Holmes wrote: Everyone keeps telling me that there is little or no difference between a stored proc and dynamic SQL, then I come across a post like this one and I think they are full of bullsh*t.
They obviously are. Just ask a simple question:
What takes more time - cooking and eating or just eating?
They should understand.
|
|
|
|
|
I could connect the database to different server thro' network, but last few days it fails to connect. It populates the Error message Failed to retrieve data for this request (Microsoft.SqlServer.Management.Sdk.Sfc.EnumeratorCore)
To my suprises I could connect the SQL Profiler. If there is network problem it wouldn't connect profiler. I think there is no problem in Network. Could anyone help me out.
Regards,
John.L.Ponratnam
|
|
|
|
|
Quick Google found this: http://forums.asp.net/t/1381599.aspx[^
me, me, me
"The dinosaurs became extinct because they didn't have a space program. And if we become extinct because we don't have a space program, it'll serve us right!"
Larry Niven
|
|
|
|
|
Hi all,
I have a legacy database system which was developed with the old MS Basic PDS ISAM. Does anyone know of a way to import the data into Access?
Thanks
|
|
|
|
|
I don't, but it sounds interesting (at least mildly).
The first step would be to find documentation of the file structure.
Or maybe the Jet Engine can read them? Did you search online?
Edit: Or an ODBC driver?
modified on Wednesday, March 31, 2010 9:44 PM
|
|
|
|
|
I haven't been able to find any documentation of the file structure, and I'd rather not go that route if possible.
An ODBC driver is exactly what I'm looking for. I have had no luck reading them with any of the Jet drivers.
I have searched online, and information on this seems to be fairly rare.
There has to be a simple solution, I'm just not seeing it!
|
|
|
|
|
I did some searching as well.
Out of curiosity... Is this a one-time copy of the data to a "better" database system? Or something you'll need to continue to do?
The best I can think of is to use that version of BASIC (I found a place from where it can be downloaded) to write a DLL with the required interface and call it from whatever language you want to use. Or maybe in the package there's a DLL you could call.
I also just took a quick look at an old (1999) ODBC book I have and nothing jumped out at me.
I'll give it some more thought after I have a nap... my brain hurts.
|
|
|
|
|
Thanks for the replies
It will probably be an ongoing thing until I can convert the program that creates it to a more modern language.
I thought about doing that, too, and tried to download it yesterday, but I got an error!! What site did you find it on?
|
|
|
|
|
|
|
Maybe try:http://www.easysoft.com/products/data_access/odbc_isam_driver
Is this free?
|
|
|
|
|
In a one-day special Topeka pointed me here[^], maybe that helps.
|
|
|
|
|
Is it C-ISAM? I saw a mention of that in my search, I think in some Jet Engine documentation.
|
|
|
|
|
I don't know, the subject line said Basic PDS ISAM (not sure what it all means), however the one link looked interesting, however it might be all but relevant.
|
|
|
|
|
Hee heeee!! I just installed MS Basic PDS 7.1 on my Win XP system!
During the install, there is the option for using a TSR for ISAM or building the routines into the program -- I chose the latter.
Now to see whether or not I can wrap the ISAM routines in a DLL that I can access from .net... 
Edit: Maybe I shouldn't have selected that option. Or maybe it just won't work on XP.
I built the ISAMDEMO app, but it keeps reporting not enough ISAM buffers.
I tried linking in the ISAM routines (see here[^]), but to no avail. 
modified on Monday, April 5, 2010 12:04 AM
|
|
|
|
|
Sorry for the late reply, I've been on vacation.
That was the same site I had tried and got an error, so I went back and tried again, clicked the button, and nothing happened, but no error. I am at work, so it's possible it's a firewall issue.
Yes, the ISAM library links in to your source when you make the executable. I never tried the TSR. I'm not exactly sure how to convert the lib to a .net dll, but that might work for me.
I'm sure I can get it working by somehow getting the pds and developing software to read it, but that doesn't really solve my problem with porting it to Access. I would have to run the program each time I needed to grab some data and then import it.
Many Thanks
|
|
|
|
|
PIEBALDconsult wrote: Edit: Maybe I shouldn't have selected that option. Or maybe it just won't work on XP.
I built the ISAMDEMO app, but it keeps reporting not enough ISAM buffers.
I tried linking in the ISAM routines (see here[^]), but to no avail.
IIRC, there may be an environment variable that needs to be set.
|
|
|
|
|
Hy,
Is there any function through which i can find the average of the not null rows only.Like total/number of rows containg data
Thankz & Ragards
,
raghvendra Panda
|
|
|
|
|
Why would you store rows that have no data?
In any case maybe what you are asking is how, for instance, do I get all records where one field is not null?
select count(*) from table where not column3 is null
might be close to what you want.
me, me, me
"The dinosaurs became extinct because they didn't have a space program. And if we become extinct because we don't have a space program, it'll serve us right!"
Larry Niven
|
|
|
|
|
I hope it will help you.
NORTHWIND Database
SELECT
COUNT(UnitPrice) AS [NoOfItems], SUM(UnitPrice) AS [TOTAL], AVG(UnitPrice) AS [AVERAGE]
FROM Products
WHERE UnitPrice IS NOT NULL
OUTPUT
NoOfItems TOTAL AVERAGE
----------- --------------------- ---------------------
77 2222.71 28.8663
|
|
|
|
|
Thnks for ur reply I had taken avg(isnull(column),0)..is that the reason why my function is also considering null rows also?
|
|
|
|
|
raghvendrapanda wrote: avg(isnull(column),0)..
The answer is yes, but as a test why don't you try the following.
avg(isnull(column),42) ..
Chris Meech
I am Canadian. [heard in a local bar]
In theory there is no difference between theory and practice. In practice there is. [Yogi Berra]
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
