|
Hi
I'm executing the following Xquery on SQLserver 2005.
select
policy_xml.query('/Policy/PolicyApplication/Inuserer/InsurerID'),
policy_xml.query('/Policy/PolicyApplication/Insurer/AccountIdentifier'),
policy_xml.query('/Policy/PolicyApplication/Insurer/Type'),
policy_xml.query('/Policy/PolicyApplication/Insurer/HolderName')
from policyTable
where
policy_xml.exist('/Policy/PolicyApplication/Insurer/PolicyOwner/EntityID[.="E_1"]') = 1
Its taking 50 Secs to search from 10000 records{without indexes}
The table has 3 columns sno,policy_id,policy_xml.
I have primary index on policy_id field and 1 secondary index(path index) on the table.
When I enable the index the query takes 380 secs.
The size of loan_xml column is about 110 Kb for each row. We need to keep the indexes for some more complex update Xqueries. Is there a way out to improve the performance of the XQuery we are using? Please let us also know the reasons of decrease in performance using indexes on the table. Do indexs have any issues related to XQuery performance?
Vikas Neema
|
|
|
|
|
i want to create hierarchical infragistics grid. anyone can u give me the idea about this?
|
|
|
|
|
If you've installed the grid, there are a number of tutorials associated with it. Plus, the Infagistics forums are a better place to ask for help.
Deja View - the feeling that you've seen this post before.
|
|
|
|
|
Pete O`Hanlon wrote: the Infagistics forums are a better place to ask for help.
Better watch out. I got voted down the other day for suggesting that the OP use the forums supplied by the third party vendor.
*mumble...grumble...ungrateful...mumble...grumble*
Upcoming events:
* Glasgow: Mock Objects, SQL Server CLR Integration, Reporting Services, db4o, Dependency Injection with Spring ...
* Reading: Developer Day 5
Never write for other people. Write for yourself, because you have a passion for it. -- Marc Clifton
My website
|
|
|
|
|
Ah - I see you're fluent in mumble as well. I like to tinge mine with a side helping of grumpy.
Deja View - the feeling that you've seen this post before.
|
|
|
|
|
Hello all
i have a min date and max date, and between these dates
i want to calculate all the weeks , and i also need
start date and end date of each week betweeen this start and
end date,, can any body guide me how to get that in sql,
thanks in advance
hello
|
|
|
|
|
for the week calculation in sql,use this code
declare @fromdate datetime
declare @ToDate datetime
declare @Days int
declare @week int
set @fromdate='2007-02-04 00:00:00.000 '
set @todate='2007-12-31 00:00:00.000 '
set @Days=datediff(day,@fromdate,@todate)
select @Days
set @week=(@Days-(@Days%7))/7
select @week
|
|
|
|
|
Hi,
I need to return column names only. My code works, but I am changing the initial column name to something more meaningful, like the ID field I changed to SKU like:
ID AS 'SKU'
Here is my code how I returned back only the column names:
' Create SQL statement
Dim strSQL = "SELECT ID AS 'SKU', ProductName AS 'Product Name' FROM bvc_Product;"
' Create instance of connection and command object
Dim conn As New SqlConnection(ConfigurationSettings.AppSettings("ConnectionString"))
Dim command As New SqlCommand(strSQL, conn)
Dim dr As SqlDataReader
Dim i As Integer
' Mark the command as a stored procedure
command.CommandType = CommandType.Text
' Execute the command
conn.Open()
dr = command.ExecuteReader(CommandBehavior.SchemaOnly)
For i = 0 To dr.FieldCount - 1
Response.Write("Column: " & dr.GetName(i).ToString() & "<br />")
Next
My problem is is that I need the result to return the original field name, and the new field name, in this case I need it to return ID and SKU, and ProductName and ProductName.
How do I do this?
Can someone please advise?
Regards
ma se
|
|
|
|
|
Hi
It is not possible to get actual column names if alias name is added. If you are passing SQL Statment from the code behind, you can write to split the query as array and extract using substring 'as' then you will get an actual column name. Make sure that you have added 'as' for every column. I think this is bit complicated.
Just try it out.
Harini
|
|
|
|
|
Please don't cross post.
Deja View - the feeling that you've seen this post before.
|
|
|
|
|
Hi All,
i did a serious mistake, and loss all the data of one financial year in sql server.
I create the script of all tables of a database, and i was inteneded to execute this script on a new database, but unfortunately i executed on the same database, now all my tables are blank (no data).
One more thing, i did not planned any backup model for this now i only have .mdf & .ldf files.
HOw i can recover it, is there any way, please suggest.
Many Thanks & Best Regards,
adnan
Many Thanks,
Adnan Rafiq
muhammadadnanrafiq@gmail.com
|
|
|
|
|
If transaction log is enabled, then you can try restoring from transaction log.
however i've never tried this. you have to refer to some articles on how to do.
Regards
KP
|
|
|
|
|
Thanks for Repley.
I tried google a lot, but dont find any suitable solution, actually i did blunder, becuase script also have drop database, and create databse with the same name, please reffer me to some article.
Many Thanks & Best Regards,
adnan
Many Thanks,
Adnan Rafiq
muhammadadnanrafiq@gmail.com
|
|
|
|
|
Sorry to say, but you are stuffed here.
Deja View - the feeling that you've seen this post before.
|
|
|
|
|
Is there any option to recover, if i tried to recover old files by using data recovery software like easyrecovery.......
Many Thanks,
Adnan Rafiq
muhammadadnanrafiq@gmail.com
|
|
|
|
|
Yes it may be possible to recover deleted files (if the disk space is not used by other files) with some data recovery tools.
Regards
KP
|
|
|
|
|
As long transaction log is present, it is possible to recover. once is truncated or deleted, i don't think is possible.
Regards
KP
|
|
|
|
|
Thanks for Helping to forget about it .
Many Thanks,
Adnan Rafiq
muhammadadnanrafiq@gmail.com
|
|
|
|
|
Hi All,
There is a requirement to create a procedure with passing Table/View name, column names in a dynamic manner with a return of resultset, It is require both in SqlServer and Sybase.
For example I am creating here one procedure:
alter procedure dba.TestProc(in viewname varchar(30),in orderbycol varchar(30),in orderbytype integer)
/* RESULT ( column-name,... ) */
begin
if orderbytype = '1' then
execute immediate 'select '+orderbycol+' from '+viewname+' order by cast('+orderbycol+' as numeric)'
else
execute immediate 'select '+orderbycol+' from '+viewname+' order by '+orderbycol
end if
end
From above procedure I am getting the error when I am using in my .NET application that it is not returning Resultset.
Suppose I want to make a procedure for:
Select dynamicCol1, dynamicCol2 from dynamicTable
How I will create the procedure for above with passing dynamicCol1,dynamicCol2 and dynamicTable variable and how I will call in .NET application.
With regards
|
|
|
|
|
Hi
Try this in Northwind database. Assume you are passing two parameters @OrderBycol and @viewname from the front end.
use northwind<br />
go<br />
<br />
DECLARE @sql nvarchar(4000)<br />
DECLARE @OrderByCol nvarchar(4000)<br />
DECLARE @viewname nvarchar(100) <br />
<br />
set @viewname = 'Invoices' -- passed from front end <br />
set @OrderByCol = 'ShipName, ShipAddress' -- passed from front end <br />
<br />
SET @sql = 'SELECT ' + @OrderByCol + ' FROM ' + @viewname<br />
<br />
print @sql<br />
<br />
EXECUTE (@sql)
and make sure that you have given one space after SELECT in 'SELECT ' and also before & after the keyword FROM clause.
Harini
|
|
|
|
|
Thanks a lot.
I will try it. Have you any idea how I will create it in Sybase.
Is there any problem that to call the Procedure in front-end as it returns a result set not a single value?
|
|
|
|
|
Hi
I have not worked on Sybase.
I dont get you the last line ...
Harini
|
|
|
|
|
Hi
Everyone
Two Tables r there.
Emp-ENO->1,2,3
Ename->A,B,C
Score-ENO->1,2,3
S1->70,96,80
S2->75,65,75
S3->90,,100
In Score Table fields ENO,Subject1,S2,S3 r there shown above.
Please help.I want to display those employee names who got same marks in Subject 2 like 75 in S2.
Please help for this query.
Thanks
CARECAREER
|
|
|
|
|
check whether this solvs
select * from score where s2 in (
select s.s2
from emp e inner join score s on e.id = s.id
group by s.s2 having count(*) > 1
)
Regards
KP
|
|
|
|
|
My web hotel has upgraded from MySQL 4.1.21 to MySQL 5.0.37. The only problem I have had with this is that a query doesn't return anything in the result any more:
select sum(i.FileSize) as TotalSize<br />
from Photos p<br />
inner join Images i on i.PhotoId=p.PhotoId and i.IsPublic=1<br />
where p.IsPublicArt=1 or p.IsPublicCommon=1
If I change "sum" to "max" or "count" the query return one row, with "sum" or "avg" it returns zero rows.
FileSize is int and can not be null.
If I test the query directly in the database (using phpmyadmin) it returns one row with all the aggregates.
My connection string looks like this (with a different uid and pwd of course): "driver={MySQL};server=mysql.loopia.se;uid=asdfasdf;pwd=asdfasdf;database=voidstation_com"
I have tried to add ";option=3" to the connection string, but that didn't make any difference.
The list of changes that the web hotel linked to:
http://dev.mysql.com/doc/refman/5.0/en/upgrading-from-4-1.html[^]
I don't see anything there that should affect this.
Anybody got any ideas?
---
single minded; short sighted; long gone;
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
