Make Microsoft SQL Run in Less than 1 Second

Closed

描述

The query takes about 7 seconds. I need it to run in less than 1 second.

--------------------------------------

select a.* from( select [url removed, login to view] rating,a.asset_id From ipm_asset a INNER JOIN CONTAINSTABLE(ipm_asset,*,'ISABOUT("NAME" NEAR "*jpg*" weight (.4),"TAGS" NEAR "*jpg*" weight (.3),"SERVICES" NEAR "*jpg*" weight (.2),"MEDIA" NEAR "*jpg*" weight (.1),"ILLUST" NEAR "*jpg*" weight (.6),"FILENAME" NEAR "*jpg*" weight (.4),FORMSOF (inflectional,"jpg") weight (.6) ,FORMSOF (thesaurus,"jpg") weight (.4) )') as ipmrank on a.asset_id = ipmrank.[KEY] where 1=1 and a.asset_id in ( SELECT asset_id FROM IPM_asset a WHERE [url removed, login to view] = 'Y' and asset_id in (select a.asset_id from asset_filetype_lookup a where [url removed, login to view] in (1,2 )) ) and [url removed, login to view] <> 84 and [url removed, login to view] <> 85 and [url removed, login to view] <> 48 and [url removed, login to view] <> 64 and [url removed, login to view] = 'Y' /*end*/ ) a join (select asset_id from ipm_asset_security Where ((security_id in (select [url removed, login to view] from ipm_group a, ipm_group_user b where [url removed, login to view] = 'Y' and [url removed, login to view] = [url removed, login to view] and [url removed, login to view] = '1') and type = 'G')) or (security_id = '1' and type = 'U') union select asset_id from ipm_asset a where a.securitylevel_id >= '0' ) d on a.asset_id = d.asset_id

技能: 微软 SQL 服务器

查看更多: run sql file, resume sql microsoft access conversion, run sql file vbnet, run sql dumps linux, run sql script automatically phpmyadmin, run sql perl, check pop3 run sql, sql microsoft project query, vb6 run sql job, run sql server 2005 script, coding java applet can run ie7 microsoft jvm, console application run sql 2005 script, microsoft access multi select listbox, run sql backup job vb6, run sql commands perl script simple

Project ID: #12148807

22 freelancers are bidding on average $60 for this job

mpalgeo

Hi I have been working with MSSQL for over 17 years. I am fully experienced as a developer and a DBA. Currently I am working at big financial organization in which I am responsible for the BI department. I have de 更多

$100 USD 在5天内
(47条评论)
5.7
youthpassion

I am a Microsoft certified solutions developer having 10 years experience of software industry in developing web, mobile and desktop applications. I have great expertise in VB, C#, C++, MVC, Asp.Net, SQL Server, JQuery 更多

$50 USD 在2天内
(1条评论)
4.8
phiona06

Hi! I'm senior lecturer on databases with 10+ years experirnce in MS SQL Server. As I see in this query need to change every IN statement to JOIN equivalent. And then I need your db with real data. May be need to add 更多

$50 USD 在2天内
(7条评论)
3.6
andreyryazanov

Hello! Your SQL statements needs to be redesigned from scratch. I will be able to make it efficient if you let me know about your tables structure and the purpose of the query.

$100 USD in 1 day
(1条评论)
3.2
fanninson45

SHOWCASE An experienced Microsoft Certified Professional with Database Programming and Administration skills across both MS SQL and Business Intelligence Development. Having the ability to take the technical lead in p 更多

$100 USD 在3天内
(5条评论)
3.3
ajoaosilva

Hi I have over 20 years of experience with SQL, Microsoft SQL Server (since version 6.5 in 1996) and Microsoft Access (since version 2.0 in 1993) so, these database systems don't have many secrets to me. Also, i'm u 更多

$50 USD 在3天内
(6条评论)
3.0
riveralf

Hello, I have more than 16 years of experience as SQL Server DBA, I am focusing on bottleneck detection and query optimization. I can help you with the issue that you are reporting on this project. Reagrds, L 更多

$50 USD 在2天内
(4条评论)
1.9
ksinghrajeev

Please provide the data and DB create script for ipm_asset, asset_filetype_lookup, ipmrank, ipm_group, ipm_group_user tables. I will try to work.

$55 USD 在2天内
(1条评论)
1.9
vw7590795vw

Please check below query and let me know. select  X.*  from  (  select  ipmrank.RANK rating, a.asset_id  From dbo.ipm_asset AS a (NOLOCK) INNER JOIN dbo.IPM_asset AS b (NOLOCK) ON b.asset_id = a.asset_id INNE 更多

$50 USD 在0天内
(2条评论)
1.8
TayNelson

I can do this, but I would need a database schema and at least some data (if not all)

$55 USD in 1 day
(0条评论)
0.0
alexarroyo

try this: select a.asset_id into #asset_id_a from asset_filetype_lookup a where a.convertertype in (1,2)) SELECT asset_id into #ipm_asset_id FROM IPM_asset a inner join #asset_id_a b on a.asset_id = b.a 更多

$50 USD in 1 day
(0条评论)
0.0
kurbsky

I have more than 10 years of experience with optimizing SQl queries. Certainly, to fulfill your project it is necessary to have DDLs and test data for all tables involved, but please try this as a first draft: sele 更多

$50 USD in 1 day
(0条评论)
0.0
Agiletechstudio

Sir I have 4 year+ experience in .net Development. I have work on Different Desktop and web applications in my Carrier. I read your basic requirement and want to some detail information about data. I will give you com 更多

$50 USD 在2天内
(0条评论)
0.0
ebonnevi

I' dedicated to my work! i'm always avalaible to help other person or companies. I'm sure we can collaborate on short or long terms.

$55 USD 在10天内
(0条评论)
0.0
seniorprgorammer

Hi, I need to have SQL Server database as well in order to optimize this query. Please provide me Thanks, Hafiz

$55 USD 在10天内
(1条评论)
0.0
$55 USD in 1 day
(0条评论)
0.0
bogusbd

Hello can you give me a database dump ? I need only tables which appear in the query Is it possible to change the table structure? Because search as this NEAR "*jpg*" very slow .. and such structures in t 更多

$70 USD in 1 day
(0条评论)
0.0
serverhint

Hi, I am currently working for a webhosting company , I have good knowledge in Windows and Linux web Technologies, as well as in Server Technologies, I have good experience in Virtualization in VMWare, Xen, KVM, H 更多

$55 USD 在2天内
(0条评论)
0.0
Maq2

A proposal has not yet been provided

$61 USD 在5天内
(0条评论)
0.0
khannouman

hi.Yes i can do this for you . I will optimize your query and it will run on 1 sec. Kindly reply me and create milestone . Hope to hear you back.Thanks

$55 USD 在2天内
(0条评论)
0.0