Encryption And Query Performance
Feb 20, 2007
I am trying to implement encryption but have run into some serious performance issues. I am required to encrypt the SSN in our database. In and of itself, this is not a problem. The problem comes in because there is also a need to be able to query the table based on the SSN. Since the SSN is encrypted, the query basically performs an index scan, decrypting each value as it goes along. As a result, the query for one record out of 10 million records in the table takes three minutes. It needs to occur immediately.
If I could encrypt my SSN parameter and then compare it to the encrypted value in the column, it would work fine. Unfortunately, everytime you encrypt a particular value, the resultant encrypted value is different. Hence, I have to decrypt the column to match my parameter, instead of encrypting th parameter to match the column.
Does anyone have any suggestions to help alleviate this problem?
Thanks,
View 8 Replies
ADVERTISEMENT
Jun 2, 2006
Hi
I am trying to encrypt data using a symmetric key which is encrypted by certificate. I do not want grant control on these objects to the users who wants to decrypt this data. Instead I have created a udf with execute context as "dbo" and used DecryptByKeyAutoCert built-in function.
Now this works fine but large data operations this is extremely slow. It takes around 10 minutes to select decrypted data whic in comparision takes 11 seconds when DecryptByKey function is used.
But I am not sure when DecryptByKey is used, whether the symmetric key is decrypted by the private key of the certificate or not. Can somebody give some explanation of this ?
Also, I can not have a UDF with these following steps
1. Open symmetric key
2. Convert secretdata using DecryptByKey
3. Close Symmetric Key.
4. return decrypted value.
Can some one give some insights on this ?
View 4 Replies
View Related
Jan 27, 2008
Hello All,
Here is the SQL 2005 encryption environment:
1. Clustered SQL 2005 (enterprise edition) on windows 2003. HP (quad processor) with CPU affinity set to all processors.
2. Table structure where encrypted data will be stored has two varbinary (max) columns to store encrypted data. The columns are varbinary (max) b/c the data size could be more that 8K.
3. Encryption using AES (tried both 128/256) algorithm with symmetric keys.
When inserting data in the columns, CPU is staying at 50% when inserting records. Any ideas why this would be happening. Any suggestions on improving performance is appreciated..
Thanks..
View 7 Replies
View Related
Aug 2, 2006
I am in the stage of design for an application that uses SQL server 2005. We intended to encrypt some sensitve data using the encryption features in SQL server 2005. we will use symmetric key encryption. The question here is which symmetric encryption algorithm has the best performance? how much does the key size affect the perfromance? the data to be encrypted will be some lines of text equal to a word document.
any ideas?
View 1 Replies
View Related
Jul 9, 2007
Does anyone have any specific performance information using AES or other encryption schemes supported by SQL Server 2005?
- What method did you find works best?
- Did you encrypt any XML Data?
- Did you encrypt specific columns or whole tables?
Thanks,
Michael
View 1 Replies
View Related
Jun 23, 2006
Hello Everyone,I have a very complex performance issue with our production database.Here's the scenario. We have a production webserver server and adevelopment web server. Both are running SQL Server 2000.I encounted various performance issues with the production server with aparticular query. It would take approximately 22 seconds to return 100rows, thats about 0.22 seconds per row. Note: I ran the query in singleuser mode. So I tested the query on the Development server by taking abackup (.dmp) of the database and moving it onto the dev server. I ranthe same query and found that it ran in less than a second.I took a look at the query execution plan and I found that they we'rethe exact same in both cases.Then I took a look at the various index's, and again I found nodifferences in the table indices.If both databases are identical, I'm assumeing that the issue is relatedto some external hardware issue like: disk space, memory etc. Or couldit be OS software related issues, like service packs, SQL Serverconfiguations etc.Here's what I've done to rule out some obvious hardware issues on theprod server:1. Moved all extraneous files to a secondary harddrive to free up spaceon the primary harddrive. There is 55gb's of free space on the disk.2. Applied SQL Server SP4 service packs3. Defragmented the primary harddrive4. Applied all Windows Server 2003 updatesHere is the prod servers system specs:2x Intel Xeon 2.67GHZTotal Physical Memory 2GB, Available Physical Memory 815MBWindows Server 2003 SE /w SP1Here is the dev serers system specs:2x Intel Xeon 2.80GHz2GB DDR2-SDRAMWindows Server 2003 SE /w SP1I'm not sure what else to do, the query performance is an order ofmagnitude difference and I can't explain it. To me its is a hardware oroperating system related issue.Any Ideas would help me greatly!Thanks,Brian T*** Sent via Developersdex http://www.developersdex.com ***
View 2 Replies
View Related
Oct 7, 2015
I have created two user defined functions for encryption and decryption using passphrase mechanism. When I call encryption function, each time I am getting the different values for the same input. While I searching a particular value, it takes long time to retrieve due to calling decryption function for each row.
best way to encrypt and decrypt using user defined functions.Below is the query which is taking long time.
SELECT ID FROM table WITH (NOLOCK)
WHERE dbo.DecodeFunction(column) = 'value'
When I try to use symetric or asymetric encryption, I am not able to put "OPEN SYMETRIC KEY" code in a function. So, I am using PassPhrase mechanism.
View 3 Replies
View Related
Feb 23, 2008
Hi,could you tell me please what are the ways of the query stringencryption in T-SQL ? I would like to have a storage procedure thatencrypts e.g. http://123.23.43.1/pagegen.asp?param1=23¶m2=124 intohttp://pagegen.asp?code=fdgfehiqrzvhe and convert it back to theorginal url.Thanks in advanceOmi
View 1 Replies
View Related
May 19, 2004
Hi..
Is there any specific place where I can find which SQL Query is more efficient?.
Is INNER JOIN is faster or Select ... Where ID in (SELECT ...) is faster?
I have two tables:
1.FLEET (The number of rows is not so much)
Attributes :
Company_Id (PK)
Fleet_Id (PK)
Fleet_Name
Fleet_Description
2.USER_PRIVILEGE (The number of rows can reach up to 3 times the number of row in fleet table)
Attributes :
Company_Id (PK)
Fleet_Id (PK)
User_Id (PK)
Privilege_Id(PK)
Comment
Category
I want to select Fleet_Id and Fleet_Name from fleet table
Where the current user has privilege_id=1
I have two possible select statement :
1.Option 1
SELECT Fleet_Name, Fleet_Id
FROM FLEET
WHERE (Company_Id = 2) AND (Fleet_Id IN
(SELECT fleet_id
FROM user_privilege
WHERE user_id = 11 AND company_id = 2 AND privilege_id = 1))
ORDER BY Fleet_Name
2.Option 2
SELECT F.Fleet_Name, F.Fleet_Id
FROM USER_PRIVILEGE U INNER JOIN
FLEET F ON U.Fleet_Id = F.Fleet_Id
WHERE (F.Company_Id = 2) AND (U.Privilege_Id = 1) AND (U.User_Id = 11)
ORDER BY F.Fleet_Name
Actually which one is faster. Is SQL Statement with INNER JOIN (Option 2) can be executed faster than the one with double Select Statement(Option 1)?
Any suggestion is welcomed.
Thank you very much.
View 1 Replies
View Related
Dec 12, 2005
Hi:
I have the following query, can somebody help me?
SELECT s.Id, s.NameFROM Switch s INNER JOIN SwitchTelephoneRange r ON s.Id = r.IdWHERE '1526858' BETWEEN FromTelephone AND ToTelephone
Where the '1526858' is a phone number.
My problem is, I want to run the above query for each record in :
select Telephone from PhoneDirectory
So, each telephone number in the second phone, would macth the ' ' in the first query.
How can I do so? Do I need a loop? a cursor? Can you help please?
Thanks
View 5 Replies
View Related
Nov 14, 2003
Hi,
I want to know query execution time as output. I want execution time only. this is for tuning purpose... Time displayed in the status bar is not helpful for me.
thanks.
View 1 Replies
View Related
Mar 25, 1999
Hi there,
I'm having a big performance problems with sql query. What i have done is changes physical layout (rearanged the columns) in one of the tables in the database. I used bcp to get the data out and then in. There are about quarter million rows in the this table. I have created the same indexes but know the same query takes a long time to execute. I have noticed that the showplan is somehow different than it used to be. This query uses the table i have changed and another one that i haven't touched. I have updated the stats to no help.
Here are the show plans.
this one is slow
STEP 1
The type of query is INSERT
The update mode is direct
Worktable created for ORDER BY
FROM TABLE
SW_PERSON
Nested iteration
Index : swiPERSON10
FROM TABLE
SW_CASE
Nested iteration
Table Scan
TO TABLE
Worktable 1
STEP 2
The type of query is SELECT
This step involves sorting
FROM TABLE
Worktable 1
Using GETSORTED Table Scan
this one used to be fast
STEP 1
The type of query is INSERT
The update mode is direct
Worktable created for ORDER BY
FROM TABLE
SW_CASE
Nested iteration
Table Scan
FROM TABLE
SW_PERSON
Nested iteration
Index : PK_SW_PERSON_1__27
TO TABLE
Worktable 1
STEP 2
The type of query is SELECT
This step involves sorting
FROM TABLE
Worktable 1
Using GETSORTED Table Scan
I think the problem is with the fact that the fist one doesn't use the PK
key which is the one the links both tables. My question is how to force the query to use this index.
PS.
One thing i haven't done is to recreate indexes on the other table. But i don't think that would have made a differnece.
Thanks
View 4 Replies
View Related
Oct 16, 2006
I have the following functions in my database
Code:
CREATE FUNCTION dbo.fnProductsRetrieveBySupplierID
(
@SupplierID int
)
RETURNS TABLE
AS
RETURN
(
SELECT *
FROM Products
WHERE SupplierID = @SupplierID
)
CREATE FUNCTION dbo.fnSuppliersRetrieveBySupplierID
(
@SupplierID int
)
RETURNS TABLE
AS
RETURN
(
SELECT *
FROM Suppliers
WHERE SupplierID = @SupplierID
)
I have been testing the performance of the following SQL statements:
Code:
1.
SELECT *
FROM Products INNER JOIN
Suppliers ON (Products.SupplierID = Suppliers.SupplierID)
WHERE Products.SupplierID = 3
2.
SELECT *
FROM dbo.fnProductsRetrieveBySupplierID (3),
dbo.fnSuppliersRetrieveBySupplierID (3)
I have built a loop to execute each statement multiple times and then compare the execution times. Although both queries produce the same result, the 2nd one (which uses the functions) is about twice as slow, does anyone know why?
thanks
View 1 Replies
View Related
May 21, 2006
Hi All,
I am joining three tables each table has got about 1.5 million rows,selecting data from these three tables and inserting into a table,to avoid transaction log issues I am running the query in a batch of size 50,000 rows,it is taking about 5hrs to insert all the 1.5 millions rows.
All the columns in the where clause have proper indexes,I ran show plan for the query and it is using indexes properly and not doing any table scan.I updated the statistics for all the indexes also.
query looks some thing like this.
insert into table d (col1,col2,col3,.............. )
values (a.col1,b.col2,c.col3 .....................)
from a,b,c
where a.id = b.id
and a.id = c.id
and a.id in between @minid and @currid
The @minid starts from 1 and @currid starts from 50000 ,I am running this in a loop, in next iteration @minid will become 50001 and currid 100,000 and so on.
Any idea why it is taking that much time?.
Thanks in advance,
bsr.
View 7 Replies
View Related
Jan 22, 2004
I have two tables.
Employee
EmployeeCode int Primary Key
Employee_Stock
EmployeeCode int
StockCode varchar(10)
Primay key on (Employeecode, StockCode)..
There is no foreign key relation between these 2 tables.
Now my question is which query give more performance. and why?
1. Select * from
Employee INNER JOIN Employee_Stock on Employee.Employeecode = Employee_Stock.EmployeeCode
2. Create a foreign Key between Employee and Employee_Stock for EmployeeCode. and run the same query.
Actually we forgot to put the foreign key relationship between these 2 tables and we have lot of queries joining them..
Now if we add foreignkey, is it going to improve the performance or not?
Thanks
Ramesh
View 1 Replies
View Related
Apr 8, 2008
I wrote a query and I use cursor in the query. When I run the query on dev box it takes 3 mins.
I moved the query to EPM database box and it takes forever to run.
Usually EPM database query performance is much better. How do I start debugging the poor performance?
How can I check if the query is creating any table locks?
Purpose of query: I get all the Companies (20000) and loop thru each company in the cursor and do calculations.
Thanks...
View 13 Replies
View Related
Jun 27, 2007
Hi all
I want to check the query performance
How I should starT
I mean what is the plan to be followed
Thanks In Advance
Malathi Rao
View 3 Replies
View Related
Mar 9, 2008
I have a query like below and it takes a aouple of seconds to run
select a.Registration_Key, ag.Agreement_Type_Name,p.ServiceProvider from dbo.Assessment a
INNER JOIN (
select distinct Registration_Key, p.ServiceProvider, max(CSDS_Object_Key) as [Sequence]
from dbo.Assessment a
INNER JOIN dbo.CD_Provider_Xref p
ON a.Provider_CD = p.Provider_CD
where Creation_DT >= '07/01/2007'
and Reason_CD = 1
group by Registration_Key, p.ServiceProvider
) as s1
ON a.CSDS_Object_Key = s1.Sequence
INNER JOIN dbo.CD_Provider_XREF p
ON a.Provider_CD = p.Provider_CD
INNER JOIN dbo.CD_Agreement_Type ag
ON ag.Agreement_Type_CD = a.Agreement_Type_CD
LEFT OUTER JOIN (
select distinct Registration_Key, p.ServiceProvider
, 1 as served
from dbo.Encounters e
INNER JOIN dbo.CD_Provider_Xref p
ON e.Provider_CD = p.Provider_CD
where Encounter_Begin_DT between '08/01/2007' and '08/31/2007'
and Procedure_CD is not null
and Encounter_Units > 0
) as s2
ON a.Registration_Key = s2.Registration_Key
and p.ServiceProvider = s2.ServiceProvider
group by a.Registration_Key, ag.Agreement_Type_Name, p.ServiceProvider
However, if i add a served field( stamped with 1) it takes forever to run.. All of join columns have indexes.. cluster and non-clustered.. and i don;t see any index fregmentaitons...
select a.Registration_Key, ag.Agreement_Type_Name,p.ServiceProvider, served from dbo.Assessment a
INNER JOIN (
select distinct Registration_Key, p.ServiceProvider, max(CSDS_Object_Key) as [Sequence]
from dbo.Assessment a
INNER JOIN dbo.CD_Provider_Xref p
ON a.Provider_CD = p.Provider_CD
where Creation_DT >= '07/01/2007'
and Reason_CD = 1
group by Registration_Key, p.ServiceProvider
) as s1
ON a.CSDS_Object_Key = s1.Sequence
INNER JOIN dbo.CD_Provider_XREF p
ON a.Provider_CD = p.Provider_CD
INNER JOIN dbo.CD_Agreement_Type ag
ON ag.Agreement_Type_CD = a.Agreement_Type_CD
LEFT OUTER JOIN (
select distinct Registration_Key, p.ServiceProvider
, 1 as served
from dbo.Encounters e
INNER JOIN dbo.CD_Provider_Xref p
ON e.Provider_CD = p.Provider_CD
where Encounter_Begin_DT between '08/01/2007' and '08/31/2007'
and Procedure_CD is not null
and Encounter_Units > 0
) as s2
ON a.Registration_Key = s2.Registration_Key
and p.ServiceProvider = s2.ServiceProvider
group by a.Registration_Key, ag.Agreement_Type_Name, p.ServiceProvider, served
View 3 Replies
View Related
Sep 12, 2006
Hi!
Sorry for bad english, I'm from Brazil.
In microsoft.public.sqlserver.ce haven't found a way to improve performance
of this query. Thanks for any help or reply!
This used to take almost 6 min !!! With index on E.Produto now takes about 30 sec...
1,909 row table
Ipaq 1950 - Samsung 300 Mhz - 32 MB RAM - Windows Mobile 5.0 - SQL CE
2.0
PK (all multiple columns) - tables:
Lotes - pk(Empresa, Lote, Contagem, Produto)
Contagem - pk(Empresa, Lote, Contagem, Produto)
Produtos - pk(Codigo) // this field also is FK <=> Produto in all other
tables
Estoque - pk(Empresa, Ordem, Produto)
Part of my VB.NET code with SQL:
sql_grd_inv = "SELECT L.Empresa, L.Lote, L.Contagem, L.Produto" _
& ", P.Unidade, P.Descr, P.Ref, P.Embgem, P.Marca" _
& ", Sum(CASE WHEN E.Estoque IS NULL THEN 0 ELSE E.Estoque END)" _
& "AS SomaEstoque, C.Qtde" _
& " FROM (" _
& "(Lotes L " _
& "LEFT JOIN Contagem C ON (L.Empresa = C.Empresa) " _
& "AND (L.Lote = C.Lote) AND (L.Contagem = C.Contagem) " _
& "AND (L.Produto = C.Produto)" _
& ") " _
& "LEFT JOIN Estoque E ON (L.Empresa = E.Empresa) " _
& "AND (L.Produto = E.Produto)" _
& ") " _
& "INNER JOIN Produtos P ON L.Produto = P.Codigo " _
& "GROUP BY L.Empresa, L.Lote, L.Contagem, L.Produto" _
& ", P.Unidade, P.Descr, P.Ref, P.Embgem, P.Marca, C.Qtde " _
& "HAVING (L.Empresa='" & IncEmpresa & "') " _
& "AND (L.Lote='" & Cbo_Lote_Pnl_Invent.Text & "') AND (L.Contagem='" _
& Cbo_Cont_Pnl_Invent.Text & "') " _
& "UNION " _
& "SELECT " _
& "C.Empresa, C.Lote, C.Contagem, C.Produto" _
& ", P.Unidade, P.Descr, P.Ref, P.Embgem, P.Marca" _
& ", Sum(CASE WHEN E.Estoque IS NULL THEN 0 ELSE E.Estoque END)" _
& "AS SomaEstoque, C.Qtde" _
& " FROM (" _
& "(Contagem C " _
& "LEFT JOIN Lotes L ON (C.Empresa = L.Empresa) " _
& "AND (C.Lote = L.Lote) AND (C.Contagem = L.Contagem) " _
& "AND (C.Produto = L.Produto)" _
& ") " _
& "LEFT JOIN Estoque E ON (C.Empresa = E.Empresa) " _
& "AND (C.Produto = E.Produto)" _
& ") " _
& "INNER JOIN Produtos P ON C.Produto = P.Codigo " _
& "GROUP BY C.Empresa, C.Lote, C.Contagem, C.Produto" _
& ", P.Unidade, P.Descr, P.Ref, P.Embgem, P.Marca, C.Qtde" _
& ", L.Empresa, L.Lote, L.Contagem, L.Produto " _
& "HAVING (L.Empresa Is Null) AND (L.Lote Is Null) " _
& "AND (L.Contagem Is Null) AND (L.Produto Is Null) " _
& "AND (C.Empresa='" & IncEmpresa & "') " _
& "AND (C.Lote='" & Cbo_Lote_Pnl_Invent.Text & "') AND (C.Contagem='" _
& Cbo_Cont_Pnl_Invent.Text & "') "
View 1 Replies
View Related
Apr 26, 2006
Hello SQL Gurus,
From the query below, I am using 2 TOP functions to return the desired row. I am wondering if someone can shed some light on how to AVOID using 2 TOP statements and combine into just one select query?
select TOP 1 * from (select TOP 2 Num from A order by Num) X order by Num desc
Truly Appreciate your help as this performance issue has been bugging in my head for quite some time...
Sincerely,
-Lawrence
View 1 Replies
View Related
Apr 16, 2008
Hi,
I am wondering if someone can clear my mind about a performance doubt.
What performs best using inner or left joins... I filter my data in the ON clauses or in the WHERE clauses...
Example 1:
FROM TABLE_A A INNER JOIN TABLE_B B ON A.ID = B.ID AND A.ID IN (@IDS)
Example 2:
FROM TABLE_A A INNER JOIN TABLE_B B ON A.ID = B.ID
WHERE A.ID IN (@IDS)
Consider that table A and B can contain large sets of data.
Best Regards,
Luis Simões
View 2 Replies
View Related
Mar 14, 2008
I usually am all over answering these kinds of questions, but while I continue to work on this issue, maybe someone here can lend me a hand. A vendor application we run, stores metadata about backup blobs stored on a NAS device. The app basically backs up select folders on 1400 remote computers in the back office of our stores, and stores this on a NAS, while maintaining metadata about the BLOBs in SQL Server so that they can push recovery of the data back to the original store it came from. The database is roughly 80GB in size and has a single file group and is on its own dedicated LUN. It uses TempDB heavily, and this is not something that I can change, but TempDB is on a different disk array.
Today I spent hours on a conference call with them looking at a specific stored procedure that is used to clean up the records in the database after a BLOB file is deleted. A single BLOB file can have millions of related records in the database. There is a LEFT JOIN in the code that is against a table with 150 million + rows of data in it. The table size is fairly small, only 5 GB of data, but the LEFT JOIN spools 2.4GB of data to a Hash Match. It seems to me like the left join can't be removed, but I don't get how all of this works, because I didn't write the application. It is an INDEX SCAN. I can't seem to eliminate it. Is there anything I can do to help this thing out?
View 5 Replies
View Related
May 7, 2007
I am attempting to get a better understanding of why my SQL 2005 setup when running a simple select statement on a large table is displaying very low IO in performance monitor. If i run a single Select * From testtable i see 4mbsec transfer and Disk readssec is around 8-9. This particular table is sitting on a single U320 10k drive so i expecting to see far more substantial IO. Does anyone have any information on how IO is consumed using different SQL operations so i can obtain a better understanding?
View 2 Replies
View Related
Jan 21, 2008
So I am experimenting with upgrading a Windows Mobile application from .NETCF 2.0 to .NETCF3.5, along with moving my SQL 2005 Compact to SQL Compact 3.5. I have a database that I upgraded using the recommended methods (creating a datasource in VS2008, opening the SQL 2005 Compact .sdf file and allowing the tool to upgrade to SQL Compact 3.5). On the device (Dell Axim x51), with the .SDF files on an SD Card, the query, when executed against the SQL 2005 Compact database file, takes 1.5 seconds, but takes 1min41sec to execute on the SQL Compact 3.5 database.
This is a fairly simple query, with an inner join (using about 4 inner join constraints), a where clause (over about 3 things), and an order by clause. The execution plan for the SQL Compact 3.5 query shows index seeks (one consuming 2% and the other consuming 0%, with the inner join using 98%). The database files are on the order of 90MB.
Can anyone offer any suggestion why the SQL Compact 3.5 query performance would be so much worse than the SQL 2005 Compact performance?
Thanks,
Matthew Belk
View 21 Replies
View Related
Apr 27, 2006
The below query seems to be very slow :
select distinct a.* from test a inner join test1 b on b.col1 = a.col1
inner join test2 c on c.col2 = a.col2 where exists (select NULL from
test3 d where (d.col3 = a.col3 or a.col3 is null))
All the columns involved in the WHERE clause and JOIN conditions have
index. Is there any alternative available for the above which can
increase the performance ?
Please advice,
Thanks,
Smitha
View 23 Replies
View Related
Feb 13, 2006
Hello,
Here I have a small doubt about validating UserName and Password.I validate username and password with following quey (forget about case-sensitiveness of password) :select password from table where username='Uname' and password='pwd';Now in second scenario, I use following :select password from table where username='Uname'and validate password in .NET code.1) If user having 'Uname' does not exists in database then which query is faster (first or second)?2.1) If user exists and password is not matching then which is faster?2.2) 2.1 + If there is clustered index on username column, is first query optimized?
Thanks
View 1 Replies
View Related
Jan 19, 2000
How could I tell the performance difference between two queries:
One is:
select * from table
where Lower(colomnname) = 'value'
The other is:
select * from table
where colomnname = 'value'
Basically the difference is in lower() function, how much this function will affect the query performance.
Is there a formal way to test it out, or by any logic.
Thanks, Mike
View 2 Replies
View Related
Sep 28, 2004
Hello,
I'm new to OLAP systems and MDX, and am doing some testing on Microsoft Analysis Service 2000 SP3, the database is Microsoft SQL Server 2000 SP3. In the cube I designed, the fact table contains purcahse information including the cost and quantity of the parts and the suppliers of the parts. There are 2 measures, qtyAvailable and cost. Two dimensions are involved, which are part and supplier. Here is what I 'm going to do:
1. calculate sum(qtyAvailable * cost * 0.0001) for all the items in the fact table, let us call this value sum1
2. find out in the fact table all of those parts with their sum( qtyAvailable * cost ) greater than sum1
Here is the MDX to do the 2 things above:
with member [Measures].[prod1] as '[Measures].[qtyAvailable] * [Measures].[cost]'
with member [Measures].[prod2] as '[Measures].[prod1] * 0.0001'
with member [Measures].[sum1] as 'sum(crossjoin([part].members, [supplier].members), [measures].[prod2])'
with member [Measures].[sum2] as 'sum(crossjoin([part].currentmember, [supplier].members), [measures].[prod1])'
select {[Measures].[sum2]} on columns,
Filter({[part].members}, ([measures].[sum2]>[Measures].[sum1]) ) on rows
from cube1
It takes 9 seconds to calculate only sum1 by using another MDX. The value of sum1 is 8256865.23.
If I replace sum1 in the MDX provided above with 8256865.23, it takes several minutes to finish.
But it keeps running for hours if I run the MDX query above with [sum1] instead of 8256865.23. So the calculation of sum1 seems to be the bottle neck. In my MDXquery, it iterates thru the members of the dimension [part]. I don't know whether [sum1] is calculated repeatedly for each iteration or not. However, Sum1 will be constant during the running of the whole MDX query. So sum1 only needs to be calculated once. I tried to use cache to improve the performance but it didn't work.
Can anyone help to tell whether there is anyway to optimize this query?
Thanks so much
Roy
View 3 Replies
View Related
Mar 13, 2006
Hi,
Please let me know the way to increase the performance of the below query :
SELECT DISTINCT a.* FROM a INNER JOIN #temp1 b on (a.col1 = b.col1 OR a.col1 IS NULL) INNER JOIN #temp2 c on (a.col2 = c.col1 OR a.col2 IS NULL)
Here, there are no indexes/pk on the columns in any table. But I am sure that the table #temp1 and #temp2 has distinct/unique values in columns col1 used here. The table 'a' has redandant values in its column used here.
Should I create pk on the columns for #temp1 and #temp2 used here. Is that enough ? Or should I also create index on the columns of the table 'a' used here.
Also please let me know is there anyother way to increase the performance of the query.
Please advice,
MiraJ
View 6 Replies
View Related
Sep 11, 2006
Hi, I want to know if anyone have any clue about the reason why this happens.
I have a table on SQL Server 7 with 320 thousand registers and when I execute a SELECT * on it, it takes about 6 seconds to give an answer. But the same table on SQL Server 2005 Ent takes about 16 seconds, Is it normal?:shocked: :shocked:
View 3 Replies
View Related
Apr 16, 2008
Hi all. I'm new to this forum and looking for some assistance.
I've run into a unique (for me) performance problem.
I have a select statement that performs fine ( < 1 second ) using one set of values in the criteria but very poorly ( > 3 minutes )using different values. In both circumstances the query returns zero rows. The query involves a parent-child join with the criteria spread across both tables.
The execution plan looks similar between the two; the difference being a few percentage points difference on some of the operations. The tuning advisor has no recommendation in case 1 but suggests a couple of additional indexes and 4 statistics in case 2.
My gut tells me that the solution is *not* applying the additional indexes/statistics but some other issue. Or it could be the sushi I just ate.
Anyway, I'm hoping someone can point me in the right direction as what to analyze to determine why simply changing a single supplied criteria value would have such a dramatic effect on performance.
View 7 Replies
View Related
Apr 18, 2008
Hi,
I have just created a logging table that I anticipate to have 10's of millions of rows (maybe 100's of millions eventually).
Basically its a very basic, narrow table, we are using it to log hits on images for a webserver.
My question is that we want to run queries that show how many rows are shown per day etc, however we want to make sure these queries which we are anticipating to be very heavy, do not slow down the system.
I have been recommended to have a seperate database (mirror/replica) for reporting so that the performance of regular activity will not be affected.
I assume this means I would need another server for this other database?
I am thinking there are probably some alternative solutions to this as well. Getting a dedicated server just for these queries really isnt an option.
In order to improvement it is not a problem to make some sacrifices. For example, having the data update every 15 minutes is more than acceptable.
I see certain websites I use employ this strategy of making data update every 15 minutes, but I am unsure what is likely going on behind the scenes. Also the queries are lightening fast when run. I am thinking that they have some sort of table that is populated with some computed data, so its quick to query.
Any thoughts or suggestions to give me some direction, are very much appreciated !
thanks once again,
Mike123
View 12 Replies
View Related
Sep 27, 2007
I have a weird problem with a query we are running.
I have a website and a web service - both are calling the exact same query with the exact same parameters.
I find that this query through the web service is significantly slower than when being run through the web site.
I've used SQL Profiler to get an idea of what's happening on the SQL server side and in the web site it hits SQL server runs in about 1 second and returns. On the web service side it hits SQL Server and times out (set to 60 seconds). I can't figure out why SQL Server would timeout with the same query (and result set) on the web service but work quickly on the web site.
If I run the same query (with the same parameters) multiple times on the web service, the second time (and subsequent) it will return but takes significantly longer (20+ seconds). I can't figure out why SQL Server would respond differently. It's a select query, no updates, no locking occurring. I can consistently get it to perform quickly on the web site and consistently get it to perform badly on the web service.
Anyone have any ideas of what it might be or have any ideas of how I might be able to dig deeper into troubleshooting what the problem might be?
note: both the web site and the web service are running from the same server with the same credentials and connection strings.
View 8 Replies
View Related
